Thanks everybody...I'm a network admin, my experience with Excel is limited.
Glad there are such helpful people around, even with such basic questions!
Much appreciated.
--
Aaron Stamboulieh - MCSA, A+
"Charley Kyd" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Sure. Suppose "Smith, Bill" (without the quotes) is in cell B2. You could
> enter these three formulas:
>
> In cell C2: =FIND(",",$B2)
> In cell D2: =TRIM(MID($B2,$C2+1,999))
> In cell E2: =LEFT($B2,$C2-1)
>
> Here, the first formula finds the position of the **first** comma in the
> name, and the other two formulas use that number to return what you want.
>
> However, you might run into a problem if your names include something
like:
> Smith, Jr., Bill. In this case, you want to split using the **last**
comma.
> To find the position of the last comma, cell C2 needs to use a more
complex
> formula:
>
> =FIND("#",SUBSTITUTE($B2,",","#",LEN($B2)-LEN(SUBSTITUTE($B2,",",""))))
>
> Here, we count the number of characters in B2, and subtract the number of
> characters with the commas stripped out, which gives us the number of
> commas. We substitute a "#" sign for that final comma, and use FIND to
> return the position of that # sign.
>
> (Other than writing a VBA function, can anyone suggest an easier way to
find
> the last occurrence of a character in text? This is the only method that
> I've found.)
>
> Good luck.
>
> Charley Kyd
>
> "Aaron Stamboulieh" <(E-Mail Removed)> wrote in message
> news:%(E-Mail Removed)...
> > I have a spreadsheet with one column that contains a list of names in
the
> > following format:
> >
> > LASTNAME, Firstname
> >
> > Is it possible to automatically split this into two columns, one for
> > lastname, and one for firstname, using the comma as the basis of where
to
> > make the split?
> >
> > --
> > Aaron Stamboulieh - MCSA, A+
> >
> >
>
>
|
Similar Threads
Linear Mode
