Text to column using formula

[Stephen_Malley]

Hi Guys,

In excel I'm trying to extract a particular part of text from some html code but the text to column function fails completely with this. I've read many posts that give formula to do this instead but I'm struggling to rewrite one to suit my needs.

Here is an example one the html codes I'm trying to split. Please consider it in column A1

<span>
<H2><!-- mp_trans_rt_start id=""1"" args=""as"" 5 -->Lonsdale Drawstring Carry Sacks<!-- mp_trans_rt_end 5 --></H2><!-- mp_trans_remove_start=""DE,FR,AT"" --><BR>An ideal no-frills solution to transporting your sports equipment or everyday good, the <STRONG>Lonsdale Drawstring Carry Sacks</STRONG> have one large compartment with drawstring closure, finished with Lonsdale branding. <BR><!-- mp_trans_remove_end=""DE,FR,AT"" --><!-- mp_trans_add=""DE,FR,AT"" <!-- mp_trans_ost_start --]> --><BR>&gt; <STRONG>Drawstring sack</STRONG> <BR>&gt; Large compartment <BR>&gt; Drawstring <BR>&gt; Lonsdale branding <BR>&gt; 48cm x 42cm x 11cm<BR><BR><!-- mp_trans_add=""DE,FR,AT"" <!-- mp_trans_ost_end --]> -->
</span>

I'm trying to extract the sizing information (48cm x 42cm x 11cm), 2nd and last line from the above string. These sizes vary in how they are written, uses different formats and units and are not always in the same place in the code but are are precursed with "<BR>&gt;" and finished with "<BR>". I tried to use in a formula to split but getting it to work is beyond my limited capabilities.

Can anyone suggest code that will work and I can drag and drop for the rest of the worksheet?

Many thanks

Stephen

[ben_hensel]

Since the <BR>&gt; tag isn't unique to the size information you can't use those to identify them.

Since the location, format, and units of the dimensions are different, you can't really use any of that, unless you build a test for each possible case maybe?

With the information you've given I can't see a formula solution, you haven't defined the input scenario enough for any text excision I can think of.

[Stephen_Malley]

Thanks for the reply. Guess I'll spend the next few hours doing it manually urghhhh!

[jason.b75]

Since the <BR>> tag isn't unique to the size information you can't use those to identify them.

Maybe not, but "cm<BR>" is unique at the end, then you just have to work backwards.

=TRIM(RIGHT(SUBSTITUTE(LEFT(A1,FIND("cm<",A1)+1),"gt; ",REPT(" ",LEN(A1))),LEN(A1)))

[ben_hensel]

Well really I was hoping you could define the input scenario more, eg, are the units always from the set {cm, in, mm} or something like that; or if they're listed in different places are there like 3 different positions instead of just wherever; or does the text string " x " show up anywhere else (eg, are they always separated by the letter x); if so, then there's still options.

It's not that it's outright impossible, it's just that the problem isn't defined enough (yet?).


EDIT:

Maybe not, but "cm<BR>" is unique at the end, then you just have to work backwards.

No it's not unique, there could be units other than centimeters used:

These sizes vary in how they are written, uses different formats and units

[AlKey]

or

Please Login or Register  to view this content.

Data Range

	A	B
1	<BR>&gt; Large compartment <BR>&gt; Drawstring <BR>&gt; Lonsdale branding <BR>&gt; 48cm x 42cm x 11cm<BR><BR><!-- mp_trans_add=""DE,FR,AT"" <!-- mp_trans_ost_end --]> -->	48cm x 42cm x 11cm

[jason.b75]

@AlKey, my first thought was similar, but it fails if the first dimension is less than 10cm or more than 999cm, although the latter does seem a little unrealistic in most scenarios.

Although I think we may have both made the mistake of assuming that all dimensions will be in cm.

edit:-

If all first dimensions are between 10 and 999 cm (inclusive) then this one works as well.

=TRIM(MID(LEFT(A1,FIND("cm<",A1)+1),FIND("cm",A1)-3,100))

[AlKey]

Here is slightly improved formula that will deal with limitations of the first one.

Please Login or Register  to view this content.

Data Range

	A	B
1	Lonsdale branding <BR>&gt; 48cm x 42cm x 11cm<BR><BR><!-- mp_trans_add=""DE,FR,AT"" <!-- mp_trans_ost_end --]> -->	48cm x 42cm x 11cm
2	Lonsdale branding <BR>&gt; 148cm x 42cm x 11cm<BR><BR><!-- mp_trans_add=""DE,FR,AT"" <!-- mp_trans_ost_end --]> -->	148cm x 42cm x 11cm
3	Lonsdale branding <BR>&gt; 2148cm x 42cm x 11cm<BR><BR><!-- mp_trans_add=""DE,FR,AT"" <!-- mp_trans_ost_end --]> -->	2148cm x 42cm x 11cm
4	Lonsdale branding <BR>&gt; 32548cm x 42cm x 11cm<BR><BR><!-- mp_trans_add=""DE,FR,AT"" <!-- mp_trans_ost_end --]> -->	32548cm x 42cm x 11cm