Name Formatting

  • Thread starter Thread starter Chris
  • Start date Start date
C

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
 
The name being in B22
=MID(B22,SEARCH(" ",B22)+1,LEN(B22)--SEARCH(" ",B22))&"_"&LOWER(LEFT(B22,2))
Regards,
Stefi

„Chris†ezt írta:
 
Hi Stefi,

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

Ross McGuinnes = mcgui_ro


Thanks.
Chris
 
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
 
Back
Top