Hi guys, I'm looking for a formula that will remove the text between the first and last space in a cell e.g. 'Mr Brian M Taylor' becomes 'Mr Taylor' in the cell next to it.
Ideally I'd like it to work when there's only 1 space as well e.g. 'Mr Taylor' displays as 'Mr Taylor' in the next cell using the same forumla as the first example.
This is a bit advanced for me (but learning all the time :D) so any help would be fantastic.
Cheers, James.
Try: =LEFT(A1,FIND(" ",A1))&TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",50)),50))
Dom
"May the fleas of a thousand camels infest the crotch of the person who screws up your day and may their arms be too short to scratch..."
Use code tags when posting your VBA code: [code] Your code here [/code]
Remember, saying thanks only takes a second or two. Click the little star to give some Rep if you think an answer deserves it.
Worked a treat, thank you!
You might want to check for the case where the cell has no spaces:
=IF(ISERR(LEFT(A4,FIND(" ",A4))),A4,LEFT(A4,FIND(" ",A4))&TRIM(RIGHT(SUBSTITUTE(A4," ",REPT(" ",50)),50)))
All credit to Dom for the tricky part.
Regards
Trevor Shuttleworth - Retired Excel/VBA Consultant
I dream of a better world where chickens can cross the road without having their motives questioned
'Being unapologetic means never having to say you're sorry' John Cooper Clarke
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks