Problem with extracting data from text file to excel table

B

bigpappi23

New Member
Joined
Feb 1, 2013
Messages
38
Hi Guys,

I have a text file which consists of the following columns:

Code: 
[TABLE="width: 320"]
<TBODY>[TR]
[TD]UPC CD</SPAN>
[/TD]
[TD]DESCRIPTION</SPAN>
[/TD]
[/TR]
[TR]
[TD]------</SPAN>
[/TD]
[TD]------------------------------</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]15929</SPAN>
[/TD]
[TD] MX BERRY</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]15929</SPAN>
[/TD]
[TD]------------------------------</SPAN>
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]MAJOR  BRAND: 10</SPAN>
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]------------------------------</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]5322</SPAN>
[/TD]
[TD]GRAPE PET</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]5676</SPAN>
[/TD]
[TD] APPLE PET</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]5677</SPAN>
[/TD]
[TD] MMJTG ORANGE PET</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]5679</SPAN>
[/TD]
[TD] MMJTG MIX BERRY PET</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]5684</SPAN>
[/TD]
[TD]MMJTG APPLE PET</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]5685</SPAN>
[/TD]
[TD]MMJTG ORANGE PET</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]5685</SPAN>
[/TD]
[TD]------------------------------</SPAN>
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]MAJOR  BRAND: 1D</SPAN>
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]------------------------------</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]15070</SPAN>
[/TD]
[TD]STRAWBERRY PET</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]15072</SPAN>
[/TD]
[TD]ZERO MIX BERRY PET</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]15436</SPAN>
[/TD]
[TD]ORANGE PET</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]15437</SPAN>
[/TD]
[TD]LEM LIME PET</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]15437</SPAN>
[/TD]
[TD]------------------------------</SPAN>
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]MAJOR  BRAND: 1K</SPAN>
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]------------------------------</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]14573</SPAN>
[/TD]
[TD]LEMON LIME PET</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]14574</SPAN>
[/TD]
[TD]FRUIT PUNCH PET</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]14575</SPAN>
[/TD]
[TD]BERRY BLAST PET</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]14579</SPAN>
[/TD]
[TD]ORANGE PET</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]15034</SPAN>
[/TD]
[TD]STRWBRRY LEMONADE</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]15456</SPAN>
[/TD]
[TD]WHITE CHERRY PET</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]15989</SPAN>
[/TD]
[TD]A MELON PET</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]16418</SPAN>
[/TD]
[TD]TROP MANGO PET</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]16418</SPAN>
[/TD]
[TD]------------------------------</SPAN>
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]MAJOR  BRAND: 1L</SPAN>
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]------------------------------</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]14565</SPAN>
[/TD]
[TD]BLAST - PET</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]14566</SPAN>
[/TD]
[TD]FRUIT PUNCH - PET</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]14713</SPAN>
[/TD]
[TD]PA GRAPE - PET</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]14714</SPAN>
[/TD]
[TD]PA ORANGE - PET</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]14714</SPAN>
[/TD]
[TD]------------------------------</SPAN>
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]MAJOR  BRAND: 1Y</SPAN>
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]------------------------------</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]5138</SPAN>
[/TD]
[TD]MXBER</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]5138</SPAN>
[/TD]
[TD]------------------------------</SPAN>
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]MAJOR  BRAND: 1Z</SPAN>
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]------------------------------</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]1868</SPAN>
[/TD]
[TD]BERRY BLD</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]1875</SPAN>
[/TD]
[TD] TROPICAL BLD</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]4888</SPAN>
[/TD]
[TD]FRUIT PUNCH</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]4891</SPAN>
[/TD]
[TD]STRWBRY PASSION</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]5362</SPAN>
[/TD]
[TD] PINEAPPLE ORG</SPAN>
[/TD]
[/TR]
</TBODY>[/TABLE]
[TABLE="width: 320"]
<TBODY>[TR]
[TD]UPC CD</SPAN>
[/TD]
[TD]DESCRIPTION</SPAN>
[/TD]
[/TR]
[TR]
[TD]------</SPAN>
[/TD]
[TD]------------------------------</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]15929</SPAN>
[/TD]
[TD] MX BERRY</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]15929</SPAN>
[/TD]
[TD]------------------------------</SPAN>
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]MAJOR  BRAND: 10</SPAN>
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]------------------------------</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]5322</SPAN>
[/TD]
[TD]GRAPE PET</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]5676</SPAN>
[/TD]
[TD] APPLE PET</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]5677</SPAN>
[/TD]
[TD] MMJTG ORANGE PET</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]5679</SPAN>
[/TD]
[TD] MMJTG MIX BERRY PET</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]5684</SPAN>
[/TD]
[TD]MMJTG APPLE PET</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]5685</SPAN>
[/TD]
[TD]MMJTG ORANGE PET</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]5685</SPAN>
[/TD]
[TD]------------------------------</SPAN>
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]MAJOR  BRAND: 1D</SPAN>
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]------------------------------</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]15070</SPAN>
[/TD]
[TD]STRAWBERRY PET</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]15072</SPAN>
[/TD]
[TD]ZERO MIX BERRY PET</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]15436</SPAN>
[/TD]
[TD]ORANGE PET</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]15437</SPAN>
[/TD]
[TD]LEM LIME PET</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]15437</SPAN>
[/TD]
[TD]------------------------------</SPAN>
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]MAJOR  BRAND: 1K</SPAN>
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]------------------------------</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]14573</SPAN>
[/TD]
[TD]LEMON LIME PET</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]14574</SPAN>
[/TD]
[TD]FRUIT PUNCH PET</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]14575</SPAN>
[/TD]
[TD]BERRY BLAST PET</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]14579</SPAN>
[/TD]
[TD]ORANGE PET</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]15034</SPAN>
[/TD]
[TD]STRWBRRY LEMONADE</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]15456</SPAN>
[/TD]
[TD]WHITE CHERRY PET</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]15989</SPAN>
[/TD]
[TD]A MELON PET</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]16418</SPAN>
[/TD]
[TD]TROP MANGO PET</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]16418</SPAN>
[/TD]
[TD]------------------------------</SPAN>
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]MAJOR  BRAND: 1L</SPAN>
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]------------------------------</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]14565</SPAN>
[/TD]
[TD]BLAST - PET</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]14566</SPAN>
[/TD]
[TD]FRUIT PUNCH - PET</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]14713</SPAN>
[/TD]
[TD]PA GRAPE - PET</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]14714</SPAN>
[/TD]
[TD]PA ORANGE - PET</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]14714</SPAN>
[/TD]
[TD]------------------------------</SPAN>
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]MAJOR  BRAND: 1Y</SPAN>
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]------------------------------</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]5138</SPAN>
[/TD]
[TD]MXBER</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]5138</SPAN>
[/TD]
[TD]------------------------------</SPAN>
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]MAJOR  BRAND: 1Z</SPAN>
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]------------------------------</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]1868</SPAN>
[/TD]
[TD]BERRY BLD</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]1875</SPAN>
[/TD]
[TD] TROPICAL BLD</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]4888</SPAN>
[/TD]
[TD]FRUIT PUNCH</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]4891</SPAN>
[/TD]
[TD]STRWBRY PASSION</SPAN>
[/TD]
[/TR]
[TR]
[TD="align: right"]5362</SPAN>
[/TD]
[TD] PINEAPPLE ORG</SPAN>
[/TD]
[/TR]
</TBODY>[/TABLE]

then i will export the data into an excel table. I'm able to do that but my problem is exporting the Major Brand. Because i'm required to create another column and in that column will be the Major Brand column and each UPC code needs to have a major brand associated with it.

For example:
UPC Code 5322,5676,5677,5679,5684,5685 is under Major Brand: 1D. So the Major Brand column needs to be filled with 1D for each UPC code. (Hope i explained it clearly :D)

can you help me out on how to code this one? please provide the code.

thank you very much
 
Sort by date Sort by votes

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.
You must log in or register to reply here.

Forum statistics

Threads
1,221,519
Messages
6,160,285
Members
451,635
Latest member
nithchun

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top