Help needed with formula

Kelly Ospina

I have a column that brings in whatever was typed into the PO field on an order form. People mostly put in their name, and often some other text to indicate their region or department. I need to find a forumula to extract just the name and display it in last, first format.

This is an example of the data:

JAMES MCDONALD 21946

JULIO CALAHORRANO

Z.CAMBON

AARON TULLOCH/ PNW

ANGEL CRUZ- NEW HIRE

ARTHUR OGLEZNEV/PNW

Leibel S

@Kelly Ospina

This could get complicated because your are relying on all the different ways someone is inputting the data.

One possible process:

Have a column (Calling it 'Parse Info') that replaces all the possible separators (in your example: .,- ,/ ,/) with only spaces. Then in another column use that to pull the last, first name.

See below example, for the Parse info you can keep on adding SUBSTITUTE if you have more possible separators.

Parse Info:

=IF(PO@row <> "", SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(PO@row, ".", " "), "/ ", " "), "/", " "), "- ", " "), "")

Name:

=IF([Parse info]@row <> "", MID([Parse info]@row, FIND(" ", [Parse info]@row) + 1, FIND(" ", [Parse info]@row + " ", FIND(" ", [Parse info]@row) + 1) - FIND(" ", [Parse info]@row) - 1) + ", " + LEFT([Parse info]@row, FIND(" ", [Parse info]@row) - 1), "")

Kelly Ospina

Hi Leibel, thank you for taking a crack at it! I tried this and got "unparsable" in one column, and "Blocked" in the next. Honestly, I think there is just too much variation in the additional characters they're using. I may have to re-think this. Thank you for your help.

Leibel S

which one was unparsable

Kelly Ospina

The Parse Info column formula.

Leibel S

What is the column called (the one that has the below info)?

JAMES MCDONALD 21946

JULIO CALAHORRANO

Z.CAMBON

AARON TULLOCH/ PNW

ANGEL CRUZ- NEW HIRE

ARTHUR OGLEZNEV/PNW

Kelly Ospina

PO# I did try updating that in the formula, but still got the same error message.

Leibel S

PO# needs brackets:

=IF([PO#]@row <> "", SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE([PO#]@row, ".", " "), "/ ", " "), "/", " "), "- ", " "), "")

Kelly Ospina

Thank you! I will give this a try. Much appreciated!