Parsing Text

[Alex O]

Is there a way to have the data in D:D parsed out as illustrated in E2:I2

<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Calibri,Arial; FONT-SIZE: 11pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 405px"><COL style="WIDTH: 214px"><COL style="WIDTH: 110px"><COL style="WIDTH: 58px"><COL style="WIDTH: 75px"><COL style="WIDTH: 105px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD> </TD><TD>D</TD><TD>E</TD><TD>F</TD><TD>G</TD><TD>H</TD><TD>I</TD></TR><TR style="HEIGHT: 19px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">2</TD><TD style="FONT-FAMILY: Arial; FONT-SIZE: 8pt">All Occasions and Bridal 312 N Elm St Lumberton, NC 28358 USA</TD><TD>All Occasions and Bridal</TD><TD style="FONT-FAMILY: Arial; FONT-SIZE: 8pt">312 N Elm St</TD><TD style="FONT-FAMILY: Arial; FONT-SIZE: 8pt">Lumberton</TD><TD style="FONT-FAMILY: Arial; FONT-SIZE: 8pt">NC</TD><TD style="TEXT-ALIGN: right">28358</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">3</TD><TD style="FONT-FAMILY: Arial; FONT-SIZE: 8pt">Bridal and Gift Gallery 123 Love Ave Tifton, GA 31794 USA</TD><TD> </TD><TD style="FONT-FAMILY: Arial; FONT-SIZE: 8pt"> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">4</TD><TD style="FONT-FAMILY: Arial; FONT-SIZE: 8pt">Brides & Bells 473 E Main St Cartersville, GA 30121 USA</TD><TD> </TD><TD style="FONT-FAMILY: Arial; FONT-SIZE: 8pt"> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">5</TD><TD style="FONT-FAMILY: Arial; FONT-SIZE: 8pt">Groom's Choice 5 Cheshire Rd Pittsfield, MA 01201 USA</TD><TD> </TD><TD style="FONT-FAMILY: Arial; FONT-SIZE: 8pt"> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">6</TD><TD style="FONT-FAMILY: Arial; FONT-SIZE: 8pt">Nikki's Creations 5815 Greenbelt Rd College Park, MD 20740 USA</TD><TD> </TD><TD style="FONT-FAMILY: Arial; FONT-SIZE: 8pt"> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">7</TD><TD style="FONT-FAMILY: Arial; FONT-SIZE: 8pt">Sam's Tailoring & Tiffany Couture 1045 Brea Mall, 1st Floor Brea, CA 92821</TD><TD> </TD><TD style="FONT-FAMILY: Arial; FONT-SIZE: 8pt"> </TD><TD> </TD><TD> </TD><TD> </TD></TR></TBODY></TABLE>

 

[Hermanito]

I don't see a common pattern in the data, so I'm afraid not...

Maybe tackle this from the source of the data? I guess this was exported from somewhere? Maybe you can adapt the exported data so that it becomes a semicolon separated file? Then you would have no problem to import it into Excel and let Excel split it correctly into columns...

 

[ukmikeb]

Alex

Try -

Excel Workbook
	D	E	F	G	H	I	J
1	Company Address	Company	Address Line	Town	State	Zip Code	Helper Address
2	All Occasions and Bridal 312 N Elm St Lumberton, NC 28358 USA	All Occasions and Bridal	312 N Elm St	Lumberton	NC	28358	312 N Elm St Lumberton
3	Bridal and Gift Gallery 123 Love Ave Tifton, GA 31794 USA	Bridal and Gift Gallery	123 Love Ave	Tifton	GA	31794	123 Love Ave Tifton
4	Brides & Bells 473 E Main St Cartersville, GA 30121 USA	Brides & Bells	473 E Main St	Cartersville	GA	30121	473 E Main St Cartersville
5	Groom's Choice 5 Cheshire Rd Pittsfield, MA 01201 USA	Groom's Choice	5 Cheshire Rd	Pittsfield	MA	01201	5 Cheshire Rd Pittsfield
6	Nikki's Creations 5815 Greenbelt Rd College Park, MD 20740 USA	Nikki's Creations	5815 Greenbelt Rd College	Park	MD	20740	5815 Greenbelt Rd College Park
7	Sam's Tailoring & Tiffany Couture 1045 Brea Mall, 1st Floor Brea, CA 92821	Sam's Tailoring & Tiffany Couture	1045 Mall, 1st Floor	Brea	CA	92821	1045 Brea Mall, 1st Floor Brea
Sheet3

Excel 2007

Cell Formulas
Range	Formula
E2	=LEFT(D2,FIND(-LOOKUP(0,-MID(D2,MIN(FIND({0,1,2,3,4,5,6,7,8,9},D2&"0123456789")),{1;2;3;4;5;6;7;8})),D2)-2)
F2	=SUBSTITUTE($J2," "&$G2,"")
G2	=TRIM(RIGHT(SUBSTITUTE($J2," ",REPT(" ",50)),50))
H2	=MID($D2,LEN($D2)-IF(ISERROR(FIND("USA",$D2)),7,11),2)
I2	=MID($D2,LEN($D2)-IF(ISERROR(FIND("USA",$D2)),4,8),5)
J2	=MID($D2,FIND(-LOOKUP(0,-MID($D2,MIN(FIND({0,1,2,3,4,5,6,7,8,9},$D2&"0123456789")),{1;2;3;4;5;6;7;8})),$D2),FIND("##",SUBSTITUTE($D2,",","##",LEN($D2)-LEN(SUBSTITUTE($D2,",",""))))-FIND(-LOOKUP(0,-MID($D2,MIN(FIND({0,1,2,3,4,5,6,7,8,9},$D2&"0123456789")),{1;2;3;4;5;6;7;8})),$D2))

which gives you the option of correcting the Town for the anomalies.

hth

@Hermanito No Pattern - Town, State, Zip Code, USA!!!!!

 

[Hermanito]

ukmikeb: when I said there is no common pattern in this data, I mean that you cannot reliably determine where to split the string to get the correct results. Of course I saw that each line contained similar data, but also that without making assumptions, you cannot have a solution for this data that fits all cases.

To see what I mean, look at line 6, where your solution makes the address into
5815 Greenbelt Rd College and the Town into Park.
Where it should be 5815 Greenbelt Rd and College Park.

If you provide a solution where you still have to manually check every result for correctness, imho that is not a solution.
Whereas my proposal to tackle the issue at the source, could lead to a solution where checking is not necessary because it will be correct everywhere!
You shouldn't judge so quickly and think you got it all figured out...

 

[ukmikeb]

Hermanito

I'll agree on one point only that the structure of the data is not rigid enough to cater for all cases.

That said, the OP has two options -

1, Go back to the data provider and request that they provide the data in a more structured fashion and put up with any possible delays before receiving the revised file or

2, Go with a solution which probably satisfies a large percentage of the cases and has the capability to make adjustments to satisfy the end result.

Here is the Sheet with the necessary adjustment made to the data -

Excel Workbook
	D	E	F	G	H	I	J
1	Company Address	Company	Address Line	Town	State	Zip Code	Helper Address
2	All Occasions and Bridal 312 N Elm St Lumberton, NC 28358 USA	All Occasions and Bridal	312 N Elm St	Lumberton	NC	28358	312 N Elm St Lumberton
3	Bridal and Gift Gallery 123 Love Ave Tifton, GA 31794 USA	Bridal and Gift Gallery	123 Love Ave	Tifton	GA	31794	123 Love Ave Tifton
4	Brides & Bells 473 E Main St Cartersville, GA 30121 USA	Brides & Bells	473 E Main St	Cartersville	GA	30121	473 E Main St Cartersville
5	Groom's Choice 5 Cheshire Rd Pittsfield, MA 01201 USA	Groom's Choice	5 Cheshire Rd	Pittsfield	MA	01201	5 Cheshire Rd Pittsfield
6	Nikki's Creations 5815 Greenbelt Rd College Park, MD 20740 USA	Nikki's Creations	5815 Greenbelt Rd	College Park	MD	20740	5815 Greenbelt Rd College Park
7	Sam's Tailoring & Tiffany Couture 1045 Brea Mall, 1st Floor Brea, CA 92821	Sam's Tailoring & Tiffany Couture	1045 Mall, 1st Floor	Brea	CA	92821	1045 Brea Mall, 1st Floor Brea
Sheet3

Excel 2007


It would have helped more if the OP had given us a slant on the number of records involved.

I have made one further assumption that the address always begins with a number and fortunately there is a capability in the solution provided to surmount that problem, should it arise.

I trust that I have given the OP some hope that there is a solution to the problem rather than providing negative thoughts.

 

[Alex O]

Gentlemen,
I appreciate both perspectives, however, I must say that ukmikeb’s solution provided exactly what I needed (and what he observed) which is a solution that works in 95% of the scenarios and prevents me from having to go back to the client and asking for a new file. Sometimes a 100% solution isn’t possible….
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o> </o>
Thanks Gents!

 

[ukmikeb]

Alex

Pleased to have helped you with your problem.

Many thanks for the feedback.

Good luck with your project.