Newbie 'RIGHT' question

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

AnonyMouse - 06 Jan 2005 20:21 GMT

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

Reply to this Message

Peo Sjoblom - 06 Jan 2005 20:47 GMT

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

Signature

Regards,

Peo Sjoblom

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

> Hi all.
>
[quoted text clipped - 23 lines]
>
> Mouse

Reply to this Message