text to column

[MartinL]

I have a very messy datafeed with no markers to define columns, for this reason I am left witth using formulas to populate cells

Has anyone any idea of how I can approach this

below is an example of what i have (Col B)
and what I need to create (Col D:H)

Sheet4

<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: 64px"><COL style="WIDTH: 743px"><COL style="WIDTH: 64px"><COL style="WIDTH: 109px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 72px"><COL style="WIDTH: 325px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD> </TD><TD>A</TD><TD>B</TD><TD>C</TD><TD>D</TD><TD>E</TD><TD>F</TD><TD>G</TD><TD>H</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">1</TD><TD> </TD><TD>RawData</TD><TD> </TD><TD>Description</TD><TD>Weight</TD><TD>Price</TD><TD>£/Kg</TD><TD>Promo</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">2</TD><TD> </TD><TD>King Prawn Tikka And Pilau Rice 475GAny 2 for £6.00 valid from 2/3/2011 until 22/3/2011£4.65 (£9.79/kg)</TD><TD> </TD><TD>King Prawn Tikka And Pilau Rice </TD><TD>475G</TD><TD style="TEXT-ALIGN: right">£4.65</TD><TD>(£9.79/kg)</TD><TD>Any 2 for £6.00 valid from 2/3/2011 until 22/3/2011</TD></TR></TBODY></TABLE>

Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4


I have managed to get the £/Kg using this formula

=MID(B2,FIND("(",B2),LEN(B2))

In G2

but am stuck on the rest

There rules appear to be

1. Description
2. Weight
3. Promo (If any)
4. Price
5. £/Kg

So I am guessing that formulas are something like this

1. start of cell to first number
2. First found number to Character "G"
3. no idea
4. find"£" to find"("-2 (there always seems to be a space between them)
5. find open and closing brackets (which I've done)

I know this is a tall order, but does anyone have any good ideas


Martin

 

[Airfix9]

Wow, that's pretty complex.

Where is the data coming from? Sometimes, when I want to take data from, say, an email or a webpage, I stage the copy and paste through notepad, that sometimes seems to preserve the column markers before I recopy and paste into Excel.

I can have a look for you but could you post some more data so that I can see the extent of the variables that need to be dealt with? Either here or pm me.

 

[Kentman]

Martin,

You're on the right lines and from what you say the structure seems to remain the same...yes?

You've already managed to get the weight so for the first bit you can work backwards to pick up anything prior to the weight - you've then got your 'Description'
You have got the cost - work back from that, and forward from you weight, to get the 'Promo' etc etc.

I would have columns that got the actual positions of the start and end of each element and then construct your strings from those but this is not really necessary as your functions can do this but I'm a bit of a pedant but hey!

Just take a logical approach and don't get hung up about always going from left to right.

HTH

 

[Kentman]

Another thing I'd do is not search for a bracket but the string "kg)" as this is likely to be unique - a bracket could appear anywhere so you might pick up the wrong string.