cells containing text and numbers

[Skibee]

Can anyone help?

I have an Excel worksheet which is data downloaded from a stock control
package.

A column named containers has data such as
CX15
BX30
SX10

In order to perform another calculation, I need to isolate the number in
each cell (ie the 15, 30, 10 etc) from the letters and multiply the number
component by numerical data in another column.

Is there any formula I can insert into a fresh column which will do this? I
have tried conditional formulas and lookup tables, but to no avail.

Am I right in suspecting that in cells containing a mix of letters and
numbers, Excel does not treat the number as a number but rather as general
text?

Any help would be much appreciated. Thanks

 

[yshridhar]

=--right(A1,2)
A1=CX10
Best wishes
Sreedhar

 

[Joel]

Look at posting at Excel Programming
http://www.microsoft.com/office/com...rosoft.public.excel.programming&lang=en&cr=US

 

[Ron Rosenfeld]

Can anyone help?

I have an Excel worksheet which is data downloaded from a stock control
package.

A column named containers has data such as
CX15
BX30
SX10

In order to perform another calculation, I need to isolate the number in
each cell (ie the 15, 30, 10 etc) from the letters and multiply the number
component by numerical data in another column.

Is there any formula I can insert into a fresh column which will do this? I
have tried conditional formulas and lookup tables, but to no avail.
=LOOKUP(9.9E+307,--MID(A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"
0123456789")),ROW(INDIRECT("1:"&LEN(A1)))))



Am I right in suspecting that in cells containing a mix of letters and
numbers, Excel does not treat the number as a number but rather as general
text?

Yes you are.
--ron

 

[Skibee]

Thank you so much - it works perfectly. I've made a note in my "idiot's
guide" for future reference.

 

[Skibee]

Thanks Ron. This worked too. I really appreciate the help I get from folk
who know so much more than I do on this site.