Creating a formula
 

In Excel 2003, how do I key a multiplication formula for a cell that contains
numbers and text.

If the data structure is consistent down the col, think it's good to split
the numbers from the text via Data Text to columns

Assume data below is in col A, A1 down

20 Text1
21 Text1
22 Text1
23 Text1
24 Text1
25 Text1

To split, select col A
Click Data Text to columns
(Delimited will be selected)
Click Next, and check "Space" in step2
Click Finish

Col A will be split into cols A and B
(Ensure you have sufficient empty cols to the right to receive the split
portions before proceeding)

Alternatively, you could also "split" the number / text in col A
into 2 adjacent cols using formulas ..

Put in B1: =LEFT(A1,SEARCH(" ",TRIM(A1))-1)+0
Put in C1: =MID(A1,SEARCH(" ",TRIM(A1))+1,99)
(the "99" in MID is just an arbitrary figure, adjust to suit)

Select B1:C1 and copy down

Then just point formulas to col B for your calcs on the numbers
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"Kimi" wrote in message
...
In Excel 2003, how do I key a multiplication formula for a cell that
contains
numbers and text.

If you split it apart into two cells (numeric and text), your life will become
much simpler.

But depending on how your data looks, you may be able to use a formula.

(Or you might get some nice techniques to split it into a couple of cells.)

Kimi wrote:

In Excel 2003, how do I key a multiplication formula for a cell that contains
numbers and text.

--

Dave Peterson

Put in B1: =LEFT(A1,SEARCH(" ",TRIM(A1))-1)+0
Put in C1: =MID(A1,SEARCH(" ",TRIM(A1))+1,99)

Slight revision to above. Use instead:
In B1: =LEFT(TRIM(A1),SEARCH(" ",TRIM(A1))-1)+0
In C1: =MID(TRIM(A1),SEARCH(" ",TRIM(A1))+1,99)
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----