Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Separating information in a text string
If you are just importing names, can you imoprt them using a 'space'
seperator instead of a comma? "Still learning@work" wrote in message ... 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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Separating information in a text string
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? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Separating information in a text string
No. I do not have access to change any information, only to download the
file from the AS400 to my desktop. "Chad" wrote: If you are just importing names, can you imoprt them using a 'space' seperator instead of a comma? "Still learning@work" wrote in message ... 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? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Separating information in a text string
On Jul 18, 1:23*pm, Still learning@work
wrote: 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? 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Separating information in a text string
On Fri, 18 Jul 2008 20:35:51 -0700 (PDT), HappySenior
wrote: On Jul 18, 1:23*pm, Still learning@work wrote: 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? 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Separating information in a text string
On Fri, 18 Jul 2008 12:23:01 -0700, Still learning@work
wrote: 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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Separating information in a text string
On Jul 19, 5:47*am, Ron Rosenfeld wrote:
On Fri, 18 Jul 2008 20:35:51 -0700 (PDT), HappySenior wrote: On Jul 18, 1:23*pm, Still learning@work wrote: 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? 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- 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Separating information in cell | Excel Worksheet Functions | |||
Separating information in one cell into several cells | Excel Discussion (Misc queries) | |||
Help - Separating numbers appearing on the right of a text string | Excel Worksheet Functions | |||
Separating a string to two fields | Excel Programming | |||
Separating Words From a Text String | Excel Programming |