98.7% Of all customers recommend us, we're so confident about our results we publish all reviews and stats
View Live Stats View Reviews
Forum home » Delegate support and help forum » Microsoft Excel Training and help » Removing initials and periods in names
Removing initials and periods in names
Resolved · Medium Priority · Version 2010
Wed 24 Oct 2012, 12:22Delegate Daniel said...
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
Wed 24 Oct 2012, 14:33Trainer Gary said...
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
Tue 30 Oct 2012: Automatically marked as resolved.
Training information:
See also:
|
Welcome. Please choose your application (eg. Excel) and then post your question. Our Microsoft Qualified trainers will then respond within 24 hours (working days). Frequently Asked Questions
Any suggestions, questions or comments? Please post in the Improve the forum thread. |
Excel tip:How to Spell Check an Excel 2010 WorksheetExcel 2010 does not automatically spell check a document. So, here's how to manually spell check a worksheet. |
