I have an Excel spreadsheet with a column containing general text data in
the format e.g:-

Roberts, M. Tracey

which I want in the format:-

Tracey Roberts

I've managed to separate into 2 cells using the comma as a delimiter but now
want to get rid of the middle inital and fullstop. Any ideas gratefully
received

Assuming that you now have:

M. Tracey

in C1, then you could use this in D1 to remove the first 3 characters
if there is a full-stop in the

=IF(ISNUMBER(SEARCH(".",C1)),RIGHT(C1,LEN(C1)-3),C1)

Copy down as required.

Hope this helps.

Pete


On Jan 15, 4:17*pm, Fran <Fran @discussions.microsoft.com wrote:
I have an Excel spreadsheet with a *column containing general text data in
the format e.g:-

Roberts, M. Tracey

which I want in the format:-

Tracey Roberts

I've managed to separate into 2 cells using the comma as a delimiter but now
want to get rid of the middle inital and fullstop. Any ideas gratefully
received

This will work on the original cell, swapping the first and last name
positions and removing the middle initial. Name is in cell A2:

=RIGHT(A2,LEN(A2)-FIND(".",A2)-1)&" "&LEFT(A2,FIND(",",A2)-1)

This will work on the original data even if there is NO middle initial (with
full stop) listed, so one formula will work for the whole column, regardless:

=IF(ISNUMBER(SEARCH(".",A2)),
RIGHT(A2,LEN(A2)-FIND(".",A2)-1)&" "&
LEFT(A2,FIND(",",A2)-1),
RIGHT(A2,LEN(A2)-FIND(", ",A2)-1)&" "&
LEFT(A2,FIND(", ",A2)-1))

Hope that helps.
--
"Actually, I *am* a rocket scientist." -- JB

Your feedback is appreciated, click YES if this post helped you.


"Pete_UK" wrote:

Assuming that you now have:

M. Tracey

in C1, then you could use this in D1 to remove the first 3 characters
if there is a full-stop in the

=IF(ISNUMBER(SEARCH(".",C1)),RIGHT(C1,LEN(C1)-3),C1)

Copy down as required.

Hope this helps.

Pete


On Jan 15, 4:17 pm, Fran <Fran @discussions.microsoft.com wrote:
I have an Excel spreadsheet with a column containing general text data in
the format e.g:-

Roberts, M. Tracey

which I want in the format:-

Tracey Roberts

I've managed to separate into 2 cells using the comma as a delimiter but now
want to get rid of the middle inital and fullstop. Any ideas gratefully
received

If you would rather keep your two cells with last name showing, use this
formula to pull Last Name only from the original cell, in place of what you
used that got you the initial, too:

=RIGHT(A2,LEN(A2)-FIND(".",A2)-1)

If there are some names with no middle initial, use this version to get just
the last name out of the original cell:

=IF(ISNUMBER(SEARCH(".",A2)),LEFT(A2,FIND(",",A2)-1),LEFT(A2,FIND(", ",A2)-1))

--
"Actually, I *am* a rocket scientist." -- JB

Your feedback is appreciated, click YES if this post helped you.


"JBeaucaire" wrote:

This will work on the original cell, swapping the first and last name
positions and removing the middle initial. Name is in cell A2:

=RIGHT(A2,LEN(A2)-FIND(".",A2)-1)&" "&LEFT(A2,FIND(",",A2)-1)

This will work on the original data even if there is NO middle initial (with
full stop) listed, so one formula will work for the whole column, regardless:

=IF(ISNUMBER(SEARCH(".",A2)),
RIGHT(A2,LEN(A2)-FIND(".",A2)-1)&" "&
LEFT(A2,FIND(",",A2)-1),
RIGHT(A2,LEN(A2)-FIND(", ",A2)-1)&" "&
LEFT(A2,FIND(", ",A2)-1))

Hope that helps.

Use Data/ Text to Column a second time on your second column, and this time
use space as the delimiter, and get it to skip the first of those two
columns.

You could have done both operations together, using comma and space as
separators, and skipping the middle column.
--
David Biddulph


"Fran" <Fran @discussions.microsoft.com wrote in message
...
I have an Excel spreadsheet with a column containing general text data in
the format e.g:-

Roberts, M. Tracey

which I want in the format:-

Tracey Roberts

I've managed to separate into 2 cells using the comma as a delimiter but
now
want to get rid of the middle inital and fullstop. Any ideas gratefully
received

I haven't tried any of all your suggestions yet but I now have 2 cols. One
contains the surname only and the other the forename and middle initial. It
is the middle initial I am trying to get rid of. Format is as follow:-

Col 1 = Roberts
Col 2 = Tracey M.

It is the M. I am trying to delete

Thanks all, for your help. This has now been sorted by using the data -
text to cols function.