Parsing Text

[Alex O]

Is there a formula (or macro) that will parse text from A4 and place in
D4
Acme Roofing & Remodeling Co.
E4
90731 AUS
F4
2401 W. Whitestone Blvd
G4
Cedar Park, TX 78613
And so on...the range is A1:A213

Any help is appreciated!

<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Arial,Arial; FONT-SIZE: 10pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 866px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD></TD><TD>A</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">1</TD><TD>SZSALESOFFICE</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">2</TD><TD></TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">3</TD><TD>Row Labels</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">4</TD><TD style="TEXT-ALIGN: left">Acme Roofing & Remodeling Co.90731AUS2401 W. Whitestone Blvd0Cedar ParkTX78613(512)267-8820</TD></TR></TBODY></TABLE>

 

[hiker95]

Alex O,

Can we see about 20 rows of raw data beginning in cell
A4?

 

[Alex O]

I need to parse out the data as described, and then have the subtotal (ie ACME 7159.62) added to the last column.
Thanks!

<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Arial,Arial; FONT-SIZE: 10pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 866px"><COL style="WIDTH: 163px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD> </TD><TD>A</TD><TD>B</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">3</TD><TD>Row Labels</TD><TD>Sum of DTOTALAMOUNT</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">4</TD><TD style="TEXT-ALIGN: left">Acme Roofing & Remodeling Co.90731AUS2401 W. Whitestone Blvd0Cedar ParkTX78613(512)267-8820</TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">5</TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">6</TD><TD style="TEXT-ALIGN: left">INV-678008</TD><TD style="TEXT-ALIGN: right">(199.58)</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">7</TD><TD style="TEXT-ALIGN: left">INV-684791</TD><TD style="TEXT-ALIGN: right">(199.58)</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">8</TD><TD style="TEXT-ALIGN: left">INV-693513</TD><TD style="TEXT-ALIGN: right">(199.58)</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">9</TD><TD style="TEXT-ALIGN: left">INV-700433</TD><TD style="TEXT-ALIGN: right">(172.88)</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">10</TD><TD style="TEXT-ALIGN: left">INV-678007</TD><TD style="TEXT-ALIGN: right">(1,000.00)</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">11</TD><TD style="TEXT-ALIGN: left">INV-684790</TD><TD style="TEXT-ALIGN: right">(1,000.00)</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">12</TD><TD style="TEXT-ALIGN: left">INV-690070</TD><TD style="TEXT-ALIGN: right">(540.00)</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">13</TD><TD style="TEXT-ALIGN: left">INV-693511</TD><TD style="TEXT-ALIGN: right">(2,056.00)</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">14</TD><TD style="TEXT-ALIGN: left">INV-702136</TD><TD style="TEXT-ALIGN: right">(1,792.00)</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">15</TD><TD style="TEXT-ALIGN: left">REV BDWO MULTI INV-</TD><TD style="TEXT-ALIGN: right">(7,159.62)</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">16</TD><TD> </TD><TD style="TEXT-ALIGN: right">7,159.62 </TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">17</TD><TD style="TEXT-ALIGN: left">StemCyte (Stanhope Group)102457AUS1589 W Industrial Park Street0CovinaCA91722(626)464-2437</TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">18</TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">19</TD><TD style="TEXT-ALIGN: left">INV-677261</TD><TD style="TEXT-ALIGN: right">(1,785.00)</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">20</TD><TD style="TEXT-ALIGN: left">Cedar Park Jewelry(Media Results)102905AUS601 Whitestone Blvd0Cedar ParkTX78613(512)259-9430</TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">21</TD><TD style="TEXT-ALIGN: left">Media Results3791717043 El Camino RealSuite 210HoustonTX77058(281)480-3377</TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">22</TD><TD style="TEXT-ALIGN: left">INV-684216</TD><TD style="TEXT-ALIGN: right">(2,457.35)</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">23</TD><TD style="TEXT-ALIGN: left">INV-693648</TD><TD style="TEXT-ALIGN: right">(639.20)</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">24</TD><TD style="TEXT-ALIGN: left">Rockywood Stone & Cabinet87451DALFirst Home Improvement dba9272 LBJ FreewayDallasTX75243(972)235-5888</TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">25</TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">26</TD><TD style="TEXT-ALIGN: left">INV-651333</TD><TD style="TEXT-ALIGN: right">(2,560.00)</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">27</TD><TD style="TEXT-ALIGN: left">INV-669365</TD><TD style="TEXT-ALIGN: right">(18.00)</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">28</TD><TD style="TEXT-ALIGN: left">INV-685210</TD><TD style="TEXT-ALIGN: right">(2,298.00)</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">29</TD><TD style="TEXT-ALIGN: left">Southern Lakes & Leisure (DR 2 Market)92471DAL00000(817)461-8557</TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">30</TD><TD style="TEXT-ALIGN: left">DR 2 Marketing661881121 S Carroll Ave. suite 2300SouthlakeTX76092(817)461-8557</TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">31</TD><TD style="TEXT-ALIGN: left">INV-639330</TD><TD style="TEXT-ALIGN: right">(9,877.00)</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">32</TD><TD style="TEXT-ALIGN: left">Buckwilds Grill and Bar93651DAL9305 Preston Rd.0FriscoTX75034(972)422-2099</TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">33</TD><TD> </TD><TD> </TD></TR></TBODY></TABLE>

 

[hiker95]

Alex O,

I can not see a pattern, that works for all examples shown below, to parse the following text to columns D thru H.

Excel Workbook
	A
36	Acme Roofing & Remodeling Co.90731AUS2401 W. Whitestone Blvd0Cedar ParkTX78613(512)267-8820
37	StemCyte (Stanhope Group)102457AUS1589 W Industrial Park Street0CovinaCA91722(626)464-2437
38	Cedar Park Jewelry(Media Results)102905AUS601 Whitestone Blvd0Cedar ParkTX78613(512)259-9430
39	Media Results3791717043 El Camino RealSuite 210HoustonTX77058(281)480-3377
40	Rockywood Stone & Cabinet87451DALFirst Home Improvement dba9272 LBJ FreewayDallasTX75243(972)235-5888
41	Southern Lakes & Leisure (DR 2 Market)92471DAL00000(817)461-8557
42	Buckwilds Grill and Bar93651DAL9305 Preston Rd.0FriscoTX75034(972)422-2099
Sheet1

Click on the Post Reply button, and just enter the word BUMP, and then click on the Submit Reply button, and someone else will assist you.