Removing a 1st initial

[nyrfan]

I have lot off cells containing names, the problem is the name are list as:

F Jones
P Diddy
etc

Is there a way to format the cells to remove the 1st initial leaving on the last name?

 

[bsnapool]

Try using the text to columns function. This will split the 1 cell into 2.

 

[nyrfan]

Huh?

 

[bsnapool]

Highlight the column witht he names, then click Data > Text to columns > Use delimited > select Space. Click OK.

 

[erik.van.geit]

Hi,

if it's always one character + a space you can use
=RIGHT(A1,LEN(A1)-2)

kind regards,
Erik

EDIT: if you can have items like
PH Jones
then use
=MID(A1,SEARCH(" ",A1)+1,999)

 

[BruceyBonu$]

Great Add-in that helps with this sort of thing.... http://xcell05.free.fr/english/index.html
function called WMID() will do what you need

otherwise try the follwing
cell you want to Parse/split is A2
cell to input formala is B2

=MID(a2,FIND(" ",a2)+1,LEN(a2)-FIND(" ",a2)+1)

 

[nyrfan]

Where do I put that formula, directly into the cell. I keep et "Circular refrence"

 

[erik.van.geit]

for your information
in =MID(a2,FIND(" ",a2)+1,LEN(a2)-FIND(" ",a2)+1)
you don't need the "+1" at the end, as far as I can see

to reduce calculationtime I simply used "999" (large number: names will never be this long)

 

[erik.van.geit]

Where do I put that formula, directly into the cell. I keep et "Circular refrence"

put formula in next column
copy down as needed
select column with formulas
copy
pastevalues (rightclick pastespecial select "values")

could be done using VBA if you want to do this often

 

[nyrfan]

Perfet Erik, thank you.

Felix