Make formatted name in one column from "last, first MI" to three seperate
columns of: 1st col-First name"first", 2nd col-Middle"middleOrMI" and 3rd
col-Last name"last". When you have a file with several rows of names you
would like to do this to, how do you do it? Going through it individual row
by row would be time consuming and monotonous.

Data Text to Columns Delimited Finish

"JBird11002" wrote:

Make formatted name in one column from "last, first MI" to three seperate
columns of: 1st col-First name"first", 2nd col-Middle"middleOrMI" and 3rd
col-Last name"last". When you have a file with several rows of names you
would like to do this to, how do you do it? Going through it individual row
by row would be time consuming and monotonous.

THANKS A LOT. You don't know how much you helped. I am a recruiter and I just
shared your helpful information to hundreds of other recruiters.

"Teethless mama" wrote:

Data Text to Columns Delimited Finish

"JBird11002" wrote:

Make formatted name in one column from "last, first MI" to three seperate
columns of: 1st col-First name"first", 2nd col-Middle"middleOrMI" and 3rd
col-Last name"last". When you have a file with several rows of names you
would like to do this to, how do you do it? Going through it individual row
by row would be time consuming and monotonous.

I've had to do tasks like this a few times, and some combination of len
(length), right, and left formulas will always get it. I usually have to
build them in steps, and then I can combine the formulas in to the individual
cell so my spreadsheet isn't 60 columns wide. To throw yours together, I got
this, starting at cell A1:

Name First MI Last
last, first M first M last

In cell b2:
=LEFT(RIGHT(A2,LEN(A2)-FIND(",",A2)),LEN(RIGHT(A2,LEN(A2)-FIND(",",A2)))-2)

In cell C2:
=RIGHT(A2,1)

In cell D2:
=LEFT(A2,(FIND(",",A2)-1))

Basically, in a nutshell, I pulled the last letter only as the MI. I found
the comma and took everything to the left of that for the last name. And the
first name was the trickiest, where I pulled everything to the right of the
comma minus the last 2 characters where the space and MI are.

HTH!

beth


"JBird11002" wrote:

Make formatted name in one column from "last, first MI" to three seperate
columns of: 1st col-First name"first", 2nd col-Middle"middleOrMI" and 3rd
col-Last name"last". When you have a file with several rows of names you
would like to do this to, how do you do it? Going through it individual row
by row would be time consuming and monotonous.

Hello, I know it's been like 2 years, but I'm using your fomula. How can I
get the last set of number in this string of numbers?
2 30362920 395.40
For the first part I have this:=LEFT(B159,1)(no problem here)
2nd part: =LEFT(RIGHT(B159,LEN(B159)-FIND("
",B159)),LEN(RIGHT(B159,LEN(B159)-FIND(" ",B159)))-6) (your formula with a
slite modification)
But how do I get the last set of numbers? I tryed this: =right(B159,(FIND("
",B159)-1)) but it did not work. I tryed changeing the last number but with
no results.

Thanks for your help, if your still around.


"BethP" wrote:

I've had to do tasks like this a few times, and some combination of len
(length), right, and left formulas will always get it. I usually have to
build them in steps, and then I can combine the formulas in to the individual
cell so my spreadsheet isn't 60 columns wide. To throw yours together, I got
this, starting at cell A1:

Name First MI Last
last, first M first M last

In cell b2:
=LEFT(RIGHT(A2,LEN(A2)-FIND(",",A2)),LEN(RIGHT(A2,LEN(A2)-FIND(",",A2)))-2)

In cell C2:
=RIGHT(A2,1)

In cell D2:
=LEFT(A2,(FIND(",",A2)-1))

Basically, in a nutshell, I pulled the last letter only as the MI. I found
the comma and took everything to the left of that for the last name. And the
first name was the trickiest, where I pulled everything to the right of the
comma minus the last 2 characters where the space and MI are.

HTH!

beth


"JBird11002" wrote:

Make formatted name in one column from "last, first MI" to three seperate
columns of: 1st col-First name"first", 2nd col-Middle"middleOrMI" and 3rd
col-Last name"last". When you have a file with several rows of names you
would like to do this to, how do you do it? Going through it individual row
by row would be time consuming and monotonous.