Question Locked

switch frist and last names

0 helpful
3 replies
Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.
Is there a function or macro to switch first and last names in an Excel cell? What if there are first, middle and last names?

3 replies

by tmalo627

I have not seen any built in function to do that. I've always just made it a practice to put first and last (and even middle) names in separate columns when doing a spreadsheet in Excel. It makes situations like this a lot easier. I know on the Office 2007 version of Excel, if you right click a cell and select Format cell, there are the default options (Numbers, Currencey, Date, etc.) There is also one at the bottom that is called custom. Maybe someone a little sharper than me knows of a way to tweak that to do what you want.

by slayer_

: mark line breaks

'Middle name
dim name:
dim posF:
dim posL:
dim mName:
name="John Dun Doe":
name=trim(name):
posF = InStr(1, name, " "):
posL = InStrRev(name, " "):

If posF=posL then:
mName="":
else:
mName = Mid(name, posF + 1, Len(name) - posF - (Len(name) - posL + 1)):
end if:
mName=left(mName,1):
vReturnVal=mName:

'First Name
dim name:
dim posF:
dim posL:
dim fName:
name="John Dun Doe":
name=trim(name):
posF = InStr(1, name, " "):
fName=left(name,posF):
vReturnVal=fName:
Set C32Ext = Nothing:

'Last Name
dim name:
dim posL:
dim lName:
name="John Dun Doe":
name=trim(name):
posL = InStrRev(name," "):
lName=right(name,Len(name)-posL):
vReturnVal=lName:

This discussion is closed to new replies.