Re-arranging Last, First name????
- Thread starter dwoodyp_6
- Start date
- Joined
- Dec 28, 2006
- Messages
- 23
- Reaction score
- 0
I know how to do this, but it looks ugly. Say you have the name Berry, Halle located at cell A1.
The "=find" function determines what number in the text string the "," is and then that tool helps find and separate the first and last names. "=len" will tell you how many characters the whole text string has. "=left" and "=right" are functions that will pull off the last and first names. And the "&" operator is used to combine pieces together.
=find(",",A1) will return the number 6, and 6-1 or 5 is the number of letters in the last name Berry. =len(A1) will return the number 12 for the total number of characters in the entire text string, including the space. Note that the number of characters of the first name "Halle" is equal to the total number of characters (12) minus the position of the "," minus one more for the space, for a total of 5.
=left(A1,5) will produce the first 5 characters (reading from the left) of cell A1, or Berry.
=right(A1,5) will produce the last 5 characters of cell A1, or Halle.
To put all of this together, you have to use a concatenation string formula that basically is "first name" plus a space plus "last name."
It looks pretty awful, but here is the whole formula:
=right(A1,len(A1)-find(",",A1)-1)&" "&left(A1,find(",",A1)-1)
and voila ... you get Halle Berry. Not a bad result, if you ask me.
whew! looks uglier than it really is. once you get the formula right for one cell, you can simply copy it to other cells to return other names.
Steve
The "=find" function determines what number in the text string the "," is and then that tool helps find and separate the first and last names. "=len" will tell you how many characters the whole text string has. "=left" and "=right" are functions that will pull off the last and first names. And the "&" operator is used to combine pieces together.
=find(",",A1) will return the number 6, and 6-1 or 5 is the number of letters in the last name Berry. =len(A1) will return the number 12 for the total number of characters in the entire text string, including the space. Note that the number of characters of the first name "Halle" is equal to the total number of characters (12) minus the position of the "," minus one more for the space, for a total of 5.
=left(A1,5) will produce the first 5 characters (reading from the left) of cell A1, or Berry.
=right(A1,5) will produce the last 5 characters of cell A1, or Halle.
To put all of this together, you have to use a concatenation string formula that basically is "first name" plus a space plus "last name."
It looks pretty awful, but here is the whole formula:
=right(A1,len(A1)-find(",",A1)-1)&" "&left(A1,find(",",A1)-1)
and voila ... you get Halle Berry. Not a bad result, if you ask me.
whew! looks uglier than it really is. once you get the formula right for one cell, you can simply copy it to other cells to return other names.
Steve
Last edited: