Left/Right? Another Option?

[Nomas]

Good Morning,

I am looking to parse the following data into separate columns on a normal basis. The format is


Data Sample


Headers

Rank Title Name Loc. Loc.2

#1 The Long Road - Bob Smith, RT, NA





I need it broken down as below

A B C D E
1 1 The Long Road Bob Smith RT NA

I have tried different ways of =Left = Right and its way to manual.


The format on the Data sample will always have a space between the Rank and the Title, a hyphen between the Title and the Name, then commas between the Loc. and Loc.2.

I appreciate any guidance.

John

 

[Robert Mika]

Data in A1:

B1=TRIM(LEFT(A2,FIND(" ",A2)))

C1=TRIM(LEFT(SUBSTITUTE(A2,B2,""),FIND(" -",SUBSTITUTE(A2,B2,""))))

D1=LEFT(SUBSTITUTE(A2,B2&" "&C2&" - ",""),FIND(",",SUBSTITUTE(A2,B2&" "&C2&" - ",""))-1)

E1=LEFT(SUBSTITUTE(A2,B2&" "&C2&" - "&D2&", ",""),2)

F1=RIGHT(SUBSTITUTE(A2,B2&" "&C2&" - "&D2&", "&E2,""),2)

 

[Nomas]

Robert,

Thanks for the response! I just noticed an error in how I posted this that messes up the 3-5th cells (also the spacing is more abundant than I first noted)

its below starting with 4 spaces in front of the # (the spacing below is doesnt show up in front of the #), and 2 spaces in each gap (not sure if that matters)


#1 The Long Road - Bob Smith RT, NA


After Smith there is no comma, I tried amending but am not sure I know which part to amend. Thank you very much for the help so far

 

[djreiswig]

Try this
Data in A1

B1=LEFT(TRIM(A1),FIND(" ",TRIM(A1))-1)
C1=LEFT(SUBSTITUTE(TRIM(A1),B1 & " ",""),FIND(" -",SUBSTITUTE(TRIM(A1),B1 & " ",""))-1)
D1=LEFT(SUBSTITUTE(TRIM(A1),B1&" "&C1&" - ",""),(LEN(SUBSTITUTE(TRIM(A1),B1&" "&C1&" - ","")))-LEN(E1 & ", " &F1)-1)
E1=RIGHT(SUBSTITUTE(TRIM(A1),", " & F1,""),2)
F1=RIGHT(TRIM(A1),2)

 

[Nomas]

Awesome! Thank you for the fix. I appreciate the help!