Text Project

[Joseph Horling]

Hi Chandoo Experts:

I've got a text project that I am working on. I've got the full name and full address in one cell. I would like to extract the text into separate first, middle, last, street no and name, city, state, and zip. Without using the text to columns feature. I've notice that a lot of videos on the subject only have first, middle, last name in one cell and the street, city, state, and zip in one cell. What if they are all combined in one cell? Can that be broken up into separate cells? I'm not sure which function to use for the last name, address, city, and state? Would that be the mid function for those? I did the first, middle and zip code, but can't figure out the rest. I would like to use your advise on the matter. Thank you for your help in advance....Joe from Michigan.

 

[AlanSidman]

Your worksheet is very confusing. I could not tell which sheet actually contained raw data and then which sheet contained the expected output from the raw data. It would serve you better to show what you have and what you want and let us give you a solution which may or may not include what you believe to be the appropriate methodology.

 

[Joseph Horling]

The main data is in the text tab..in a3-a7 is an example of where the full name and address with the city, state, and zip in one cell. In column C, I extracted the first name using the left function. In column D, I used the mid function with three finds to locate the spaces. In column I, I used the right function to locate the zip code. Not sure what to use in Column F (Street No. and street name), Column G(City), or Column H (State). I was looking at the mid function for those but not sure. What do you think?

 

[srinidhi]

Hi, I also face a similar situation in my work, I just apply the text to column and again concatenate on the relevant column(I sort on the last relevant column and then concatenate , like in your data the pain area is the Street No. and Name and the City.
concatenate :

4515​

North

Silver

for city :

Pine

Drive

Bellaire

I know its not a straight forward solution, but this works for me.

 

[Joseph Horling]

Thanks srinidhi, I did that too. But I was hoping to get it a formula....

 

[Nashraze]

Hi,
please select the text

Joseph Lee Horling 18794 Jamestown Circle Northville, Michigan 48168 then go to data click TEXT To column Fixed width then click next button again click next button then last finish it will show your data separate separate each coulmn.

 

[Nashraze]

attached for your reference

 

[Joseph Horling]

Hi Nashraze,
I'm still looking for the formula to use in this situation. Thanks, Joe

 

[Peter Bartholomew]

Tricks like
= TRIM( MID( SUBSTITUTE( text, " ", REPT(" ", 100) ), 1 + 100*{0,1,2,3,4,5,6}, 100 ) )
offer a 'normal' approach to splitting text at a separator but you still have to identify which substring fits what heading.