Text function and text to column project

[Joseph Horling]

Hi Chandoo Experts,

I understand how to extract names and address if they are in separate cells. However I am having difficulty trying to get them in different columns if they are grouped in one cell such as a3-a7. I got the first, middle name and the zip code. I am having trouble with the last name, street no city, and state. Tried different things but still not right. Also, when I use the text to column feature it doesn't work with the information in one cell, it is all off. Is there anyway to correct that? I put a second sheet for that called Text to column. Well thanks for helping me on this issue. Joe, from Michigan

 

[NARAYANK991]

Hi ,

What you want done is not so easy because the text itself does not follow the rules strictly.

The first , middle and last names , as well as the numeric portions such as street number and zip code can be extracted without much difficulty , but the street name and the city are problematic , because some street names are just one word , while others may have more than one word in them.

For instance :

Slayton

Brown St. , Jamestown Circle

North Silver Pines

How exactly are we going to recognize these different items as the same field ?

We could have done it provided the City and State fields had only one word in them , so that they could have been extracted , and then what ever is left would be the street name , but in the data you have given , even that is not possible.

For instance :

Northville , Bellaire

Grand Haven , Garden City

Unless we can formulate rules , with every field and every row of data adhering to those rules , I don't see how we can extract every item accurately and reliably.

Narayan