CODEWITHSUNDEEP
excelexcel-formulaformula
pkchu95
pkchu95

Reputation: 31

Separating Data in Excel with formuals

I have to separate the following screenshot into the corresponding columns at the top. I was able to figure out column D & F; however, I am having trouble separating the rest of the problem.

Constraint:

  1. I cannot use the Text-To-Column format.

  2. Zip codes must be 5 digits -> the leading 0 gets lost when string are translated to numbers

  3. I need to use formulas along the lines of right/left/mid/find/search/etc.

The Role formula is: 

  =MID(A2, SEARCH(",",A2) + 1, SEARCH(",",A2,SEARCH(",",A2)+1) - SEARCH(",",A2) - 1)

The City is:

  =LEFT(A2,FIND(",",A2)-1)

enter image description here

Upvotes: 0

Views: 64

Answers (2)

Ron Rosenfeld
Ron Rosenfeld

Reputation: 60259

Not simple, but if you have Excel 2013+ with the FILTERXML function, you can use the following formulas:

First Name: =LEFT(FILTERXML("<t><s>" & SUBSTITUTE($A1,",","</s><s>") & "</s></t>","//s[3]"),
FIND(" ",FILTERXML("<t><s>" & SUBSTITUTE($A1,",","</s><s>") & "</s></t>","//s[3]"))-1)

Last Name: =MID(FILTERXML("<t><s>" & SUBSTITUTE($A1,",","</s><s>") & "</s></t>","//s[3]"),
FIND(" ",FILTERXML("<t><s>" & SUBSTITUTE($A1,",","</s><s>") & "</s></t>","//s[3]"))+1,99)

Role:  =FILTERXML("<t><s>" & SUBSTITUTE($A1,",","</s><s>") & "</s></t>","//s[2]")

Branch:  =FILTERXML("<t><s>" & SUBSTITUTE($A1,",","</s><s>") & "</s></t>","//s[6]")

City:  =FILTERXML("<t><s>" & SUBSTITUTE($A1,",","</s><s>") & "</s></t>","//s[1]")

State:  =FILTERXML("<t><s>" & SUBSTITUTE($A1,",","</s><s>") & "</s></t>","//s[4]")

Zip:  =TEXT(FILTERXML("<t><s>" & SUBSTITUTE($A1,",","</s><s>") & "</s></t>","//s[5]"),"00000")
  • Using the commas to define the nodes, we create an XML
  • We then use the position parameter \\s[postion] to decide which node to extract
  • We also look for the space to separate the first from the last names.
    • As written, this will extract the first word as first name, and the second and any subsequent words as the last name.
    • If you have entries with just a single name word, just modify the $A1 in those two formulas to $A1 & " "
  • We also used the TEXT function to format the Zip as 5 digits. If you have 5+4, you'll need to make the appropriate change in the format code.

enter image description here

Upvotes: 0

shrivallabha.redij
shrivallabha.redij

Reputation: 5902

Equivalent formula of Text-To-Columns would be like below.

=TRIM(MID(SUBSTITUTE(","&$A2,",",REPT(" ",199)),199*1,199))

Depending on your requirement change number 1 in 199*1 by the field number i.e.

first field corresponds to 1

second field corresponds to 2

and so on...

Implement for the first data row and then copy down.

Edit:

If this data was to be sequentially split then following approach could have been easier instead of hard coded number...

=TRIM(MID(SUBSTITUTE(","&$A2,",",REPT(" ",199)),COLUMNS($A$1:A$1)*199,199))

Copy down and across!

** Edit2: **

For finding out first name and last name above formula will need addition of LEFT or RIGHT along with the same SUBSTITUTE & TRIM construct.

First name : =TRIM(LEFT(SUBSTITUTE(TRIM(MID(SUBSTITUTE(","&$A2,",",REPT(" ",199)),3*199,199))," ",REPT(" ",199)),199))

Last Name : =TRIM(RIGHT(SUBSTITUTE(TRIM(MID(SUBSTITUTE(","&$A2,",",REPT(" ",199)),3*199,199))," ",REPT(" ",199)),199))

Assumes you have at least two words separated by one space minimum!

Upvotes: 3

Related Questions