Position of characters

[arjethi]

Hey, I have a question if I have a column like the following

John Smith
Jacob Crosby (Leader)
Doug Johnson

Is there any combinations of functions which will allow me to find the position of the character of the last name for all names? For instance I've used functions but they give me the position of the ) on the (Leader) where as I just want the position of the y.

Thanks

 

[mikerickson]

This will return the location of the character immediately before the second space in a string. If A1 holds "Jacob Crosby (Leader)"

=FIND(CHAR(5), SUBSTITUTE(A1 & " ", " ", CHAR(5), 2))-1 returns 12 and

=MID(A1, FIND(CHAR(5), SUBSTITUTE(A1 & " ", " ", CHAR(5), 2))-1, 1) returns "y"

The underlined string constants (A1 & " ") are two spaces.

 

[mikerickson]

If you want to key on the "(" rather than the second space, you might use

=LEN(TRIM(LEFT(A1, FIND(CHAR(5), SUBSTITUTE(A1&"(", "(", CHAR(5)))-1)))

 

[arjethi]

The second one didn't seem to work unless I messed up something while inputting, the first set of functions were perfect however. Another question I have is, a function which would allow me to find the length of just the last name.

=LEN(REPLACE(A5,1,FIND(" ",A5),"")), this one works however again it includes the brackets in the length.


Thanks.

 

[Aladin Akyurek]

The second one didn't seem to work unless I messed up something while inputting, the first set of functions were perfect however. Another question I have is, a function which would allow me to find the length of just the last name.

=LEN(REPLACE(A5,1,FIND(" ",A5),"")), this one works however again it includes the brackets in the length.


Thanks.

Try...

=LEN(TRIM(REPLACE(LEFT(A5,FIND("(",A5&"(")-1),1,FIND(" ",A5),"")))