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

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
[email protected]
"mikeburg" <[email protected]> wrote in
message news:[email protected]...
>
> 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" <[email protected]> wrote in
message news:[email protected]...
>
> 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" <[email protected]> wrote in
> message news:[email protected]...
> >
> > 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 <[email protected]>,
mikeburg <[email protected]>
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

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
--