• 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

  • Textproject.xlsx
    13.3 KB · Views: 17
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?
 
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.
 
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.
 
attached for your reference
 

Attachments

  • step1.JPG
    step1.JPG
    166.3 KB · Views: 6
  • step2.JPG
    step2.JPG
    161.8 KB · Views: 5
  • step3.JPG
    step3.JPG
    158.3 KB · Views: 3
  • step4 - finsih.JPG
    step4 - finsih.JPG
    153.4 KB · Views: 3
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.
 
Back
Top