Creating a formula

[Guest]

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

 

[Max]

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

 

[Dave Peterson]

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.)

 

[Max]

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)