Chop up a string of text (Name, Nationality, Team)

[3G]

Hello-

I've been practicing some text processing with the LEN, MID, FIND, etc formulas, and, am having some problems figuring out how to break out the various data points.


Data is A11:A28 and structured all in one cell as follows:


Firstname Lastname (Nationality) Team


I'm trying to break each section of data out into it's own column:


Here's where I am so far:

B11 =LEFT(A11,(FIND(" (",A11))) - for the whole name

C11 = =LEFT(A11,FIND(" ",A11,1)-1) - for the first name

D11 = =RIGHT(B11,LEN(B11)-FIND(" ",B11,1)) - for the last name

E11 = =MID(A11,((FIND("(",A11))),5) for the Nationality

F11 = =RIGHT(A11,LEN(A11)-FIND(") ",A11,1)) for the team.


My main question is how to use just A11 in the case of the LAST NAME (D11) rather than create another column in B11 of the first and last name.

 

[fred]

Try


LEFT(RIGHT(A11,LEN(A11)-FIND(" ",A11,1)),FIND(" ",RIGHT(A11,LEN(A11)-FIND(" ",A11,1)),1)-1)


btw, your ,5 on nationality only count 5 letters of the nationality. So if you have (British) you only get to see (brit

 

[Luke M]

Another way to tackle the problem would be instead of trying to determine when x-character appears, force the word to a size so that you can chop it at a specific point. E.g, force a bunch of spaces to appear, and then you can trim the word:

=TRIM(MID(SUBSTITUTE(A11,"(",REPT(" ",999)),FIND(" ",A11)+1,999))


fun thing with formulas is there is no "one right way". It's fun to figure out different ways to accomplish goals. =)

 

[3G]

Gotcha Luke! I agree. The problem I sometimes have is visualizing the "math" (i.e. Subtract the length of one word from another/etc.) and it creates a "gap" in my creativity .

 

[3G]

fred- your solution is more the direction I was trying. I was layering Rights, Lefts, and, what not upon each other and fell down the slippery slope. Also, the nationality was indeed 3 chars (i.e. (Ger))


Thanks for the help!

 

[fred]

Thanks Luke, too!


Learnt something new.