help with get just the name out

[rcirone]

I have this and I need to get the first and last name out so I can use them for a vlookup please help

Richard XXXXXX 1993446666
Tom XXXXXXX 1993444444

I am using this
=RIGHT(G7,SEARCH("",G7,10))
but I need to use the name now

 

[JoeMo]

Assuming there's always a 10-digit number at the end:

Excel Workbook
	A	B
1	Richard XXXXXX 1993446666	Richard XXXXXX
2	Tom XXXXXXX 1993444444	Tom XXXXXXX
Sheet9

 

[jtakw]

Hi,

If we're assuming there's Always a 10 digit number at the end that we Don't want, then we don't need the SEARCH?

=TRIM(LEFT(A1,LEN(A1)-10))

Edit:

Also, if the formula in OP's post #1 is to extract the 10 digit number, then shouldn't it be just:

=RIGHT(G7,10)

May be I'm mis-understanding....

 

[rcirone]

how can I add a , to the names just after the name like
Richard, XXXXXX 1993446666
Tom, XXXXXXX 1993444444

 

[jtakw]

how can I add a , to the names just after the name like
Richard, XXXXXX 1993446666
Tom, XXXXXXX 1993444444

Do you mean After extracting the name? Or just like you have it in Post #4 with the numbers?

 

[jtakw]

Here's both:

Book1
	A	B	C
1	Richard XXXXXX 1993446666	Richard, XXXXXX	Richard, XXXXXX 1993446666
2	Tom XXXXXXX 1993444444	Tom, XXXXXXX	Tom, XXXXXXX 1993444444
Sheet201

Cell Formulas
Range	Formula
B1	=SUBSTITUTE(TRIM(LEFT(A1,LEN(A1)-10))," ",", ",1)
C1	=SUBSTITUTE(A1," ",", ",1)

Formulas copied down.

 

[rcirone]

That work great but I have one more thing to ask how can I add (ICE) at the end.
Richard, XXXXXX ICE

Thanks for all your help

 

[jtakw]

Like this:

Book1
	A	B
1	Richard XXXXXX 1993446666	Richard, XXXXXX ICE
2	Tom XXXXXXX 1993444444	Tom, XXXXXXX ICE
Sheet201

Cell Formulas
Range	Formula
B1	=SUBSTITUTE(TRIM(LEFT(A1,LEN(A1)-10))," ",", ",1)&" ICE"

 

[rcirone]

Thank you so much for your help I have it all done. and now my boss which is nuts like me to swap the first and last name now.

 

[jtakw]

Will there be Last names with 2 words?

Perhaps you should show 5 to 10 samples of possible data, and show the results you want.