Fromula to take employee # out of employee name field

[mikeburg]

I have employee names & numbers in column A. For example, in A1 ther
is

John A Doe 157

What would be the best formula or function to put only the employe
(without the employee number) in cell B1?

I tried =Left(A1, 10) but the number of characters from the left is no
always 10. They can vary from 6 to 30.

Any ideas? mikebur

 

[Dave O]

If the white space after Doe is a series of spaces, you can use this
formula:
=MID(A1,1,FIND(" ",A1,1)-1)
This uses the MID() function and returns the characters that occur
ahead of 3 blank spaces in the cell.

 

[Guest]

If the format was always the same, ie FirstName MiddleInitial LastName
EmployeeNumber, then you could use the Data > TextToColums > Delimited, using
space as a delimiter......this would separate each secion of the text into
it's own column.......then you could just CONCATENATE the name back together
if you wish..

Vaya con Dios,
Chuck CABGx3

 

[Don Guillett]

see if this works to get a number from the string

=VALUE(RIGHT(A1,LEN(A1)-FIND(" ",A1)))

 

[Max]

Think you could also try this option taken from a post by Harlan:
( http://tinyurl.com/amdm3 )

" .. If the ending substring could be variable length but always preceded by
a space, it's possible to use

=LEFT(TRIM(A1),LOOKUP(2,1/(MID(TRIM(A1),ROW(INDIRECT("1:256")),1)="
"),ROW(INDIRECT("1:256")))-1)

This relies on the functionality of the LOOKUP formula as it's worked
from Excel 97 through Excel 2003 (and probably in earlier versions as
well). "

 

[Dave Peterson]

An unfortunate linebreak.

=LEFT(TRIM(A1),LOOKUP(2,1/(MID(TRIM(A1),ROW(INDIRECT("1:256")),1)=" "),
ROW(INDIRECT("1:256")))-1)

(all one cell)

 

[Domenic]

Here's another way...

=TRIM(LEFT(A1,SEARCH("^^",SUBSTITUTE(A1,"
","^^",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))))

Hope this helps!

 

[Max]

Thanks, Dave !

 

[mikeburg]

Thank you for everyones wonderful help.

mikeburg

 

[Max]

You're welcome, Mike !

With the plethora of responses,
you're really spoilt for choice here ! <g>