Newbie 'RIGHT' question

  • Thread starter Thread starter AnonyMouse
  • Start date Start date
A

AnonyMouse

Hi all.

Is there a way to tell the RIGHT function to extract all the characters in a
string preceding a second capital letter?

We have a data set that contains concatenated Firstnames and Lastnames. For
example:

JoeSmith
JonathanJones
JamieJensen

We need to break the first names out into a separate column... I know how to
use RIGHT to grab a fixed number of characters or even characters preceding
a space... But in this case, I need to tell it to grab everything preceding
a CAPITAL letter.

Any ideas?

Much appreciated!!

Mouse
 
To get the last name

=MID(A1,MATCH(1,(CODE(MID(A1,ROW(INDIRECT("2:"&LEN(A1))),1))<91)*(CODE(MID(A1,ROW(INDIRECT("2:"&LEN(A1))),1))>64),0)+1,255)

entered with ctrl + shift & enter


first name

=LEFT(A1,MATCH(1,((CODE(MID(A1,ROW(INDIRECT("2:"&LEN(A1))),1))<91)*(CODE(MID(A1,ROW(INDIRECT("2:"&LEN(A1))),1))>64)),0))

also entered with ctrl + shift & enter

of course once you have one or the other you can use a simple substitute,
assume you use the latter to get the first name (name in A1), and the
formula in B1
in C1 for the last name use

=SUBSTITUTE(A1,B1,"")

entered normally

--
Regards,

Peo Sjoblom

(No private emails please, for everyone's
benefit keep the discussion in the newsgroup/forum)
 
Back
Top