Fromula to take employee # out of employee name field
 

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

John A Doe 157

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

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

Any ideas? mikeburg


--
mikeburg
------------------------------------------------------------------------
mikeburg's Profile: http://www.excelforum.com/member.php...o&userid=24581
View this thread: http://www.excelforum.com/showthread...hreadid=466249

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.

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

"mikeburg" wrote:


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

John A Doe 157

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

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

Any ideas? mikeburg


--
mikeburg
------------------------------------------------------------------------
mikeburg's Profile: http://www.excelforum.com/member.php...o&userid=24581
View this thread: http://www.excelforum.com/showthread...hreadid=466249

see if this works to get a number from the string

=VALUE(RIGHT(A1,LEN(A1)-FIND(" ",A1)))
--
Don Guillett
SalesAid Software

"mikeburg" wrote in
message ...

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

John A Doe 157

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

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

Any ideas? mikeburg


--
mikeburg
------------------------------------------------------------------------
mikeburg's Profile:
http://www.excelforum.com/member.php...o&userid=24581
View this thread: http://www.excelforum.com/showthread...hreadid=466249

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). "

--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"mikeburg" wrote in
message ...

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

John A Doe 157

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

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

Any ideas? mikeburg


--
mikeburg
------------------------------------------------------------------------
mikeburg's Profile:
http://www.excelforum.com/member.php...o&userid=24581
View this thread: http://www.excelforum.com/showthread...hreadid=466249

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)

Max wrote:

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). "

--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"mikeburg" wrote in
message ...

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

John A Doe 157

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

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

Any ideas? mikeburg


--
mikeburg
------------------------------------------------------------------------
mikeburg's Profile:
http://www.excelforum.com/member.php...o&userid=24581
View this thread: http://www.excelforum.com/showthread...hreadid=466249


--

Dave Peterson

Here's another way...

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

Hope this helps!

In article ,
mikeburg
wrote:

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

John A Doe 157

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

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

Any ideas? mikeburg

Thanks, Dave !
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--

Thank you for everyones wonderful help.

mikeburg


--
mikeburg
------------------------------------------------------------------------
mikeburg's Profile: http://www.excelforum.com/member.php...o&userid=24581
View this thread: http://www.excelforum.com/showthread...hreadid=466249

You're welcome, Mike !

With the plethora of responses,
you're really spoilt for choice here ! <g
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--