cells containing text and numbers
 

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

cells containing text and numbers
 

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

"Skibee" wrote:

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

cells containing text and numbers
 

Look at posting at Excel Programming
http://www.microsoft.com/office/comm...&lang=en&cr=US

"yshridhar" wrote:

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

"Skibee" wrote:

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

cells containing text and numbers
 

On Mon, 21 Jul 2008 03:35:01 -0700, Skibee
wrote:

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

cells containing text and numbers
 

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


"yshridhar" wrote:

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

"Skibee" wrote:

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

cells containing text and numbers
 

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.

"Ron Rosenfeld" wrote:

On Mon, 21 Jul 2008 03:35:01 -0700, Skibee
wrote:

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