Separating information in a text string

[Chad]

If you are just importing names, can you imoprt them using a 'space'
seperator instead of a comma?

 

[Still learning@work]

I have imported information from an AS400 into an Excel spreadsheet. I need
to separate the employee name field. Currently the name field is "Brown,
Oscar J". All names vary in length. What is the easiest way to split this
into separate columns under Last Name, First Name, and Middle Initial?

 

[Still learning@work]

No. I do not have access to change any information, only to download the
file from the AS400 to my desktop.

 

[HappySenior]

I have imported information from an AS400 into an Excel spreadsheet.  Ineed
to separate the employee name field.  Currently the name field is "Brown,
Oscar J".  All names vary in length.  What is the easiest way to split this
into separate columns under Last Name, First Name, and Middle Initial?

I am using Excel 2003 and assume that your spreadsheet has name data
starting in cell A2 since you are using A1 to head last name. If the
contents of cell A2 is "Brown, Oscar J.", I would:
1) Double click the dividing line between columns A and B (above row
1). This will widen column A for the largest entry in the column
2) Highlight (Select) the entire column except for the header row.
3) Select menu item DATA (top of form) then "Text to Columns"
4) A conversion wizard will appear with a sample of the data and a dot
in the Delimited option. Click on Next.
5). Place a check in ONLY the following boxes: "Treat consecutive
delimiters as one" and delimiters chosen shoul;d be comma and space.
Click on Next
6) The column data format should be text. Click finish and sit back
to admire your work. Aren't you amazed at how swiftly ypu did that
file conversion?

Inspect your work to see if you like the way, Jr., Sr., etc. are set
up.
Remember, the easiest way is to have someone else do the job...

Don (the retiree) in Montana

 

[Ron Rosenfeld]

I am using Excel 2003 and assume that your spreadsheet has name data
starting in cell A2 since you are using A1 to head last name. If the
contents of cell A2 is "Brown, Oscar J.", I would:
1) Double click the dividing line between columns A and B (above row
1). This will widen column A for the largest entry in the column
2) Highlight (Select) the entire column except for the header row.
3) Select menu item DATA (top of form) then "Text to Columns"
4) A conversion wizard will appear with a sample of the data and a dot
in the Delimited option. Click on Next.
5). Place a check in ONLY the following boxes: "Treat consecutive
delimiters as one" and delimiters chosen shoul;d be comma and space.
Click on Next
6) The column data format should be text. Click finish and sit back
to admire your work. Aren't you amazed at how swiftly ypu did that
file conversion?

Inspect your work to see if you like the way, Jr., Sr., etc. are set
up.
Remember, the easiest way is to have someone else do the job...

Don (the retiree) in Montana

And if the name is something like:

de la Hunt, Oscar J.


???
--ron

 

[Ron Rosenfeld]

I have imported information from an AS400 into an Excel spreadsheet. I need
to separate the employee name field. Currently the name field is "Brown,
Oscar J". All names vary in length. What is the easiest way to split this
into separate columns under Last Name, First Name, and Middle Initial?

If there are only two strings after the comma, you could use the
Data/Text-to-Columns wizard.

Run it the first time selecting only "comma" as the delimiter.

Then select the second column, and run it the second time using <space> as the
delimiter.

------------------------
Formula solution:

This assumes that names may or may not have a middle initial; and also that
there may or may not be a <dot> after the middle initial.

If all MI's are just a single letter, with no <dot>, the formula could be
simplified.

Last Name: =LEFT(A1,FIND(",",A1)-1)

First Name:
=IF(LEN(TRIM(RIGHT(SUBSTITUTE(SUBSTITUTE(A1,".","")," ",
REPT(" ",99)),99)))=1,MID(A1,FIND(",",A1)+2,FIND(CHAR(1),
SUBSTITUTE(A1," ",CHAR(1),LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))-
FIND(",",A1)-2),MID(A1,FIND(",",A1)+2,99))

MI:
=IF(LEN(TRIM(RIGHT(SUBSTITUTE(SUBSTITUTE(A1,".","")," ",
REPT(" ",99)),99)))=1,TRIM(RIGHT(SUBSTITUTE(
SUBSTITUTE(A1,".","")," ",REPT(" ",99)),99)),"")
--ron

 

[HappySenior]

And if the name is something like:

de la Hunt, Oscar J.

???
--ron- Hide quoted text -

- Show quoted text -

Good point, Ron. My response was for the easiest way, not the most
perfect way. I would visually inspect the output file for abberations
since you never know what will come up with foreign names and other
unique ways of people identifying themselves. The proposed formula
solution is a little much for a newbie who may not know how to cut and
paste.