I have got most of this formula working ok but I cant work out how to do the length.
I have a name that I am trying to split (I am wanting the first name)
Currently I have =MID(A2, SEARCH(", Dr ",A2,1)+5, 15) but That is 15 characters. What I would like is to take it to the next SPACE after.
IE
Smith, Dr John Peter Andrew would return just John and
Jones, Dr Elizabeth Mary Louise would return Elizabeth
Can anyone help me finish off the formula please.
Still using Excel 2007?
Glenn
None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU
no.. office 365. i will update my profile
Maybe:
=LEFT(MID(A1, SEARCH(", Dr ",A1,1)+5, 255),SEARCH(" ",MID(A1, SEARCH(", Dr ",A1,1)+5, 255)))
if you have a more modern excel, there are cleaner alternatives.
Cleaner version for O365:
=TEXTBEFORE(TEXTAFTER(A1,"Dr ")," ")
that works fine. ty.. but i am getting an #value error id its just Smith, Dr John as it doesnt have that extra space on the end. i suppose i could use the right function as an iferror
Here's another way in O365. It splits the text by the spaces into an array and then picks the 3rd item:
=INDEX(TEXTSPLIT(A2," "),3)
True, but it would fall over for non-hyphenated double-barreled surnames.
=TEXTBEFORE(TEXTAFTER(A1&" ","Dr ")," ")
will ensure that it works in the case mentioned at Post 6.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks