• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Text Project

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.
 

Attachments

AlanSidman

Active Member
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.
 
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

Active Member
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​
NorthSilver
for city :

PineDriveBellaire

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

Nashraze

New Member
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.
 

Peter Bartholomew

Well-Known Member
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.
 
Top