Extract data from string and vlookup this data to specific columns on separate worksheet

[k_mak]

Hi All, I am new to Excel Forum and in desperate need of help.
I am not sure if the below has been covered/resolved or not.
Here goes...
I have 2 worksheets:
1st worksheet is marked: Data
2nd worksheet is marked: Template
Please see attached.

In the worksheet: Data...A1 is a string consisting of Name, Address, City, State and Zip Code
In the worksheet: Template...there is specific columns to separate the Name, Address, City, State, Zip Code.

Also in the worksheet: Data...A2 as an example to advise that 'lengths' of the text and spaces between text, can be different as well.

Can someone please kindly guide how to 'extract' the necessary data from the one string in the Data worksheet into the appropriate columns in the Template(another worksheet) by means of Vlookup and Formula/function.
Please no VBA as I am not versed in it.

[rylo]

Hi

Is it right that you will always have 3 spaces between the name and the address, and the address and the city? Is the zip always going to be 5 chars long? Is the state always going to be 2 chars long?

rylo

[k_mak]

@ rylo
The spaces between name and address...will vary.
The spaces between address and city...will also vary.
The zip is max 5 chars long...however, please note it may have a 0 in front.
The state will always be 2 char.

[dyuk]

Please see the attached file for the formulas.

And I have made 2 assumptions:
(1) Name, Address, City and (State+ZIP) are separated by 3 spaces.
(2) State and ZIP are separated by 1 space.

[k_mak]

@dyuk
Thank you so much for the reply. How would I adjust the formula if the spaces is not constant?

[dyuk]

@dyuk
Thank you so much for the reply. How would I adjust the formula if the spaces is not constant?

I can't think of a solution in this way, since I would not be able to distinguish between name, address, etc.

But it would still be possible if:
1. there is a full list of possible Cities
2. all addresses start with numbers
Or other things to help to distinguish the fields.

[e.isayev]

Hi, In TEMPLATE sheet
a2 =LEFT(TRIM(DATA!A1),MATCH(1,ISNUMBER(MID(TRIM(DATA!A1),ROW(INDIRECT("1:"&LEN(TRIM(DATA!A1)))),1)+0)*1,0)-1) --- extracts names until the first numbers. Names can be 2 or more words
b2 = =LEFT(SUBSTITUTE(TRIM(SUBSTITUTE(DATA!A1,TEMPLATE!A2," "))," ","-",3),FIND("-",SUBSTITUTE(TRIM(SUBSTITUTE(DATA!A1,TEMPLATE!A2," "))," ","-",3),1)-1) -- extracts 3 words after the names
c2 =MID(SUBSTITUTE(TRIM(DATA!A1),TEMPLATE!B2,"-"),FIND("-",SUBSTITUTE(TRIM(DATA!A1),TEMPLATE!B2,"-"),1)+2,FIND(TEMPLATE!D2,SUBSTITUTE(TRIM(DATA!A1),TEMPLATE!B2,"-"),1)-FIND("-",SUBSTITUTE(TRIM(DATA!A1),TEMPLATE!B2,"-"),1)-3) -- extraxts what is between state and adress
d2 =RIGHT(TRIM(SUBSTITUTE(TRIM(DATA!A1),TEMPLATE!E2," ")),2) --- extracts two letters before zip
e2 =RIGHT(TRIM(DATA!A1),5) ---- extarcts last five numbers. considers as text. number 0 will be shown if any calculations will not be implemented

[k_mak]

@eisayew
Sorry to only reply today as was using it yesterday. The formula you gave worked perfectly.

One last question...because I am creating a template, I noticed when I copied the formula, row 3 and onwards, Columns: A, B, C is showing #N/A(as there is no data). Is there some way to "hide" the #N/A with a Blank yet keep the formula?

[e.isayev]

@eisayew
Sorry to only reply today as was using it yesterday. The formula you gave worked perfectly.

One last question...because I am creating a template, I noticed when I copied the formula, row 3 and onwards, Columns: A, B, C is showing #N/A(as there is no data). Is there some way to "hide" the #N/A with a Blank yet keep the formula?

Yes you can,
Just add Iferror function. For example =IFERROR({the whole formula above},"")

[e.isayev]

If you use excel 2003 then iferror will not work. For 2003 version you can use =if(iserror),"",formula) For the a2 cell it will be like that
=IF(ISERROR(LEFT(TRIM(DATA!A1),MATCH(1,ISNUMBER(MID(TRIM(DATA!A1),ROW(INDIRECT("1:"&LEN(TRIM(DATA!A1)))),1)+0)*1,0)-1)),"",LEFT(TRIM(DATA!A1),MATCH(1,ISNUMBER(MID(TRIM(DATA!A1),ROW(INDIRECT("1:"&LEN(TRIM(DATA!A1)))),1)+0)*1,0)-1))

[k_mak]

@eisayew
Sorry to get back to you today...I will try the (iserror) within the formula.
I was using all the formulas that you had given graciously and found that if Column B cell exceeds 3 words, then the formula does not work properly.
For example, the address, although, my example was 3 words, I ran into an occasion where the Address can be as long as 5 words which affected the State and ZIP columns.

Is there a way to use in the formula a: more than 1 space, as a point of reference? Meaning if the spaces between:
1 ) Name and Address is more than 1, than the full Address will appear in Column B
2 ) Address and State is more than 1, than the Full State will appear in Column C

Please let me know. Thanks!!!!

[e.isayev]

Hi,
I replaced formula in B column with
=MID(SUBSTITUTE(DATA!A1," ","*"),FIND("*",SUBSTITUTE(DATA!A1," ","*"),1)+1,FIND("*",SUBSTITUTE(DATA!A1," ","*"),FIND("*",SUBSTITUTE(DATA!A1," ","*"),1)+1)-FIND("*",SUBSTITUTE(DATA!A1," ","*"),1)-1)
After entering this formula other columns will take proper information. After it I think there shouldnt be proble. If there let me know