Parsing a name
Hi,
I'm using Excel to convert relatively unformatted text into something more structured. My problem is specifically with converting a full name into separate fields. The data looks something like: Smith, Sam S Jones_ Jim J Jr. Taylor Tom T. Neat Ned Friendly, Dr You get the picture. It doesn't matter to me if I use VB or a worksheet formula, or some other mechanism. This can't be the first time that anyone has converted concatinated names into seperate fields. Any advice? -- Programmer on Budget |
Parsing a name
The first step will probably be to "clean" the data. If you really have some
names separated by commas, underscores, spaces, etc. it would be helpful to pick one and replace the others with that category. I am thinking that spaces will be easiest since you might want to keep the spaces for some names like Von Flue James. Once you have them separated with spaces you can use the VBA split function to break each name into an array separated by spaces. Names = split(Name(x)) Then you can manipulate the Names array to pull out the first and last names. Dan "Budget Programmer" wrote: Hi, I'm using Excel to convert relatively unformatted text into something more structured. My problem is specifically with converting a full name into separate fields. The data looks something like: Smith, Sam S Jones_ Jim J Jr. Taylor Tom T. Neat Ned Friendly, Dr You get the picture. It doesn't matter to me if I use VB or a worksheet formula, or some other mechanism. This can't be the first time that anyone has converted concatinated names into seperate fields. Any advice? -- Programmer on Budget |
Parsing a name
if u are not using vb then
save the name in a single cell then insert as many columns u want to the Right side of the cell select all cells (newly inserted+cells containing name) and use "text to column " on data menu -- hemu "Dan Hatola" wrote: The first step will probably be to "clean" the data. If you really have some names separated by commas, underscores, spaces, etc. it would be helpful to pick one and replace the others with that category. I am thinking that spaces will be easiest since you might want to keep the spaces for some names like Von Flue James. Once you have them separated with spaces you can use the VBA split function to break each name into an array separated by spaces. Names = split(Name(x)) Then you can manipulate the Names array to pull out the first and last names. Dan "Budget Programmer" wrote: Hi, I'm using Excel to convert relatively unformatted text into something more structured. My problem is specifically with converting a full name into separate fields. The data looks something like: Smith, Sam S Jones_ Jim J Jr. Taylor Tom T. Neat Ned Friendly, Dr You get the picture. It doesn't matter to me if I use VB or a worksheet formula, or some other mechanism. This can't be the first time that anyone has converted concatinated names into seperate fields. Any advice? -- Programmer on Budget |
All times are GMT +1. The time now is 07:35 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com