Name Formatting

[Chris]

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

 

[Stefi]

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

„Chris†ezt írta:

 

[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

 

[Jarek Kujawa]

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

 

[Jarek Kujawa]

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

 

[Chris]

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


Chris

 

[Bob Phillips]

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

 

[Jarek Kujawa]

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

 

[pgarcia]

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