filter text

[willemeulen]

Am building a sheet to quickly do a price analysis of materials, as the
prices are updated almost monthly I would prefer to copy and past the
data into excel which works, only one of the colums contains all
information which I need to break up for excell to use it for a vlookup
function.:Bgr

I have the following text in a column.

90X6M DUROFLO UPVC P/PIPE CL9
140X6M DUROFLO UPVC P/PIPE CL12

I want to exctract the 90 and the 9 for the first one and the second
140 and 12 etc, all values are similar in writing but just contain
different numbers.

Thanks,

W

 

[Teethless mama]

=LEFT(A1,SEARCH("X",A1)-1)
=RIGHT(A1,LEN(A1)-SEARCH("CL",A1)-1)

 

[Rick Rothstein]

If there is *always* an "X" after the first number and an CL in front of the
last number, then 'Teethless mama' has given you the answer you need.
However, if this will not always be the case, then you can try the
following:

Return leading number from a cell...

=LOOKUP(9.9E+307,--LEFT(A1,ROW($1:$99)))

Return the ending number from a cell...

=LOOKUP(9.9E+307,--RIGHT(A1,ROW($1:$99)))