Formula need to separate mixed text from a single column into separate columns

Adam Kinney · 08/02/21

Using the LEFT or RIGHT formula currently as a means to separate the [Sales Rep Info] column. Problem is I can only figure out how to do it by number of characters.

I need the formula to separate first and last name into a new column and a second formula to capture the information in the parenthesis.

Same for the [Customer Info] column. Need numbers to go into one column, and text into another.

So four formulas total I am assuming?

Sarah Keortge · 08/02/21

This is by no means the only way to do this, but here's one way to parse out the Sales Rep Info column into its 3 pieces, using a different formula in each of the destination columns - for this example, I'm adding First, Last, and Rep Code columns.

Let's start with the Rep Code column to extract the data between the parenthesis. The formula I came up with has 5 functions (someone else might suggest something more elegant):

1) FIND to locate the position of the left parenthesis "(" in the cell,

2) LEN to count how many characters are in the cell,

3) subtract the FIND result from the LEN result to calculate the # of right-hand characters to extract

4) RIGHT to return the characters (from #3) to the right of the left parenthesis

5) SUBSTITUTE to strip out the right parenthesis ")" from the result

=SUBSTITUTE(RIGHT([Sales Rep Info]@row, (LEN([Sales Rep Info]@row) - FIND("( ", [Sales Rep Info]@row))), ")", "")

Next, is getting the First name. This formula uses 2 functions:

1) FIND to locate the space " " between the first and last name,

2) LEFT to return all characters to the left of that space's position which will just be the first name

=LEFT([Sales Rep Info]@row, FIND(" ", [Sales Rep Info]@row))

Finally, you can extract the Last name by once again finding the position of that left parenthesis and returning all of the text to the left of it and then simply removing the first name by substituting it with a non-character:

1) FIND to locate the position of the left parenthesis "(" in the cell,

2) LEFT to return the characters left of the parenthesis minus 1 (the value returned from the FIND function is inclusive of the character you're finding so in order to exclude the left parenthesis, you'll need to subtract 1). This will return both the first and last name but then...

3) SUBSTITUTE to find the text string in the First column and substitute in a null "" value which effectively strips out the first name

=SUBSTITUTE(LEFT([Sales Rep Info]@row, FIND("( ", [Sales Rep Info]@row) - 1), First@row, "")

Caveat: this all assumes you only ever have just first and last name so if you have data that includes a middle name, you'll need to determine which destination column the middle name goes in and then tweak both name column formulas to account for the second occurrence of a space within the name, accordingly.

A similar approach can be used for parsing the Customer Info column, again assuming that the data structure is the same from record to record, you would use FIND to locate the dash "-". Returning the customer's ID would be similar to what we did for First name; returning the customer's name would be similar to the Rep Code.

In total, you will have 5 different formulas: 3 for the Sales Rep Info and 2 for the Customer Info.

Sarah Keortge · 08/03/21

For keeping the whole name intact, you can use FIND for the left parenthesis position and then LEFT to return that number of characters minus 1 to exclude the parenthesis character:

=LEFT([Sales Rep Info]@row, FIND("( ", [Sales Rep Info]@row) - 1)

Sarah Keortge · 08/02/21