Daniel has attended:
Excel Advanced course
Excel VBA Intro Intermediate course
Removing initials and periods in names
Hello,
I have a wooksheet with a list of names of staff members on it. Currently they come in a two forms. For example:
1. Jack Smith
2. Jack K. Smith
I need to get excel to remove the K. etc but also leave the names that don't have any middle inital intact. I need to do this as I am running a Vlookup and the list I am comapring this too does not have any initials and thus is not working correctly for all rows.
This formula removes the middle letter but it merges the name so it is displayed as "JackSmith" instead of "Jack Smith".
=IF(LEN(A1)-LEN(SUBSTITUTE(A1," ",""))=2,LEFT(A1,FIND(" ",A1)-1)&RIGHT(A1,LEN(A1)-FIND("@",SUBSTITUTE(A1," ","@",2))),A1)
Thanks in advance.
Daniel
RE: Removing initials and periods in names
Hi Daniel
Thanks for getting in touch. A very small alteration is required to your formula to allow this to happen. Luckily, you'd already done the hard bit!
=IF(LEN(A1)-LEN(SUBSTITUTE(A1," ",""))=2,LEFT(A1,FIND(" ",A1)-1)&" "&RIGHT(A1,LEN(A1)-FIND("@",SUBSTITUTE(A1," ","@",2))),A1)
The only small difference is adding " "& before RIGHT, which includes a space before adding the surname.
You may also wish to consider the Text To Columns feature. Under the Data menu choose Text To Columns > Delimited > check Space. The data will then be broken up whereever a space occurs.
I hope this helps.
Kind regards
Gary Fenn
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
