Hi All,

I would like to know if its possible for Excel to take a persons name and
format it so it will take the first 5 letters of the Surname and the first 2
letters of the first name and produce the formatted result into an empty
cell.. i.e

Chris Jones = jones_ch

there must be a "_" inbetween the surname and first name.

Any help on this would be great.

Thank you.
Chris

The name being in B22
=MID(B22,SEARCH(" ",B22)+1,LEN(B22)--SEARCH(" ",B22))&"_"&LOWER(LEFT(B22,2))
Regards,
Stefi

€žChris€� ezt Ã*rta:

Hi All,

I would like to know if its possible for Excel to take a persons name and
format it so it will take the first 5 letters of the Surname and the first 2
letters of the first name and produce the formatted result into an empty
cell.. i.e

Chris Jones = jones_ch

there must be a "_" inbetween the surname and first name.

Any help on this would be great.

Thank you.
Chris

Hi Stefi,

This works great.. but is it possible to limit the surname to just 5 letters?
like:

Ross McGuinnes = mcgui_ro


Thanks.
Chris


"Stefi" wrote:

The name being in B22
=MID(B22,SEARCH(" ",B22)+1,LEN(B22)--SEARCH(" ",B22))&"_"&LOWER(LEFT(B22,2))
Regards,
Stefi

€žChris€� ezt Ã*rta:

Hi All,

I would like to know if its possible for Excel to take a persons name and
format it so it will take the first 5 letters of the Surname and the first 2
letters of the first name and produce the formatted result into an empty
cell.. i.e

Chris Jones = jones_ch

there must be a "_" inbetween the surname and first name.

Any help on this would be great.

Thank you.
Chris

=RIGHT(A1,LEN(A1)-FIND(" ",A1))&"_"&LEFT(A1,2)

=LEFT(RIGHT(A1,LEN(A1)-FIND(" ",A1)),5)&"_"&LEFT(A1,2)

fantasic, thank you.
this has saved me alot of time, thanks again!


Chris

"Jarek Kujawa" wrote:

=RIGHT(A1,LEN(A1)-FIND(" ",A1))&"_"&LEFT(A1,2)

=MID(A1,FIND(" ",A1)+1,5)&"_"&LEFT(A1,2)

--
__________________________________
HTH

Bob

"Chris" wrote in message
...
Hi Stefi,

This works great.. but is it possible to limit the surname to just 5
letters?
like:

Ross McGuinnes = mcgui_ro


Thanks.
Chris


"Stefi" wrote:

The name being in B22
=MID(B22,SEARCH(" ",B22)+1,LEN(B22)--SEARCH("
",B22))&"_"&LOWER(LEFT(B22,2))
Regards,
Stefi

"Chris" ezt írta:

Hi All,

I would like to know if its possible for Excel to take a persons name
and
format it so it will take the first 5 letters of the Surname and the
first 2
letters of the first name and produce the formatted result into an
empty
cell.. i.e

Chris Jones = jones_ch

there must be a "_" inbetween the surname and first name.

Any help on this would be great.

Thank you.
Chris

see Bob's formula
it's better/more concise

Hello sir,
I'm trying to use the below with a slite modification:
=MID(B158,FIND(" ",B158)+1,5)
How ever, the number in the mid is not always 5 digits long. Is there a way
to have that change or count the mid number and bring back only that number?
I would also need to do the same thing but for the number on the right.
1 4210210 134.40
2 30362920 395.40
3 820401-2 116.20
4 728435514 152.06
5 820401-1 194.57
6 820501-1 365.90
7 42111438 158.50

Thanks

"Bob Phillips" wrote:

=MID(A1,FIND(" ",A1)+1,5)&"_"&LEFT(A1,2)

--
__________________________________
HTH

Bob

"Chris" wrote in message
...
Hi Stefi,

This works great.. but is it possible to limit the surname to just 5
letters?
like:

Ross McGuinnes = mcgui_ro


Thanks.
Chris


"Stefi" wrote:

The name being in B22
=MID(B22,SEARCH(" ",B22)+1,LEN(B22)--SEARCH("
",B22))&"_"&LOWER(LEFT(B22,2))
Regards,
Stefi

"Chris" ezt Ã*rta:

Hi All,

I would like to know if its possible for Excel to take a persons name
and
format it so it will take the first 5 letters of the Surname and the
first 2
letters of the first name and produce the formatted result into an
empty
cell.. i.e

Chris Jones = jones_ch

there must be a "_" inbetween the surname and first name.

Any help on this would be great.

Thank you.
Chris