Excel 97 summing problem

[Morrism]

I have some data that looks like this:

442.48/2/221.24 (this is left justified in the cell)

and have used this formula:

=LEFT(A1,FIND("/",A1)-1)

to return 442.48 in the cell below, and

=RIGHT(A1,LEN(A1)-FIND("*",SUBSTITUTE(A1,"/","*",LEN(A1)-LEN(SUBSTITUTE(A1,"/",""))))


to return 221.24 in the cell below that.

The only problem is that I can't get Excel 97 (SR2) to sum the 442.4
and 221.24 (it always returns 0). It appears that there is som
underlying formatting or formual reference that I can't fix.

Can anyone help

 

[Peo Sjoblom]

One way

=--LEFT(A1,FIND("/",A1)-1)

and


=--RIGHT(A1,LEN(A1)-FIND("*",SUBSTITUTE(A1,"/","*",LEN(A1)-LEN(SUBSTITUTE(A1
,"/","")))))

now you can sum

 

[DNF Karran]

The problem is being caused as the LEFT and RIGHT functions return tex
strings that may look like numbers but aren't so can't be summed.

There are several ways round this, will leave you to pick the best on
for you:

Instead of using LEFT and RIGHT use Text To Columns from the data menu
This will change the values to numbers and separate them out.

Copy and paste values from the calcualtion, format as number and F
into the cells and enter out (Awful method but works)

Create the following function in a module:

Function ConvertToNum(stText) As Double
ConvertToNum = stText
End Function

and enter =ConvertToNum(LEFT(A1,FIND("/",A1)-1)) as the calculation
The result will now be a number.

Hope that helps


Dunca

 

[Paulw2k]

=VALUE(LEFT(A1,FIND("/",A1)-1))
=VALUE(RIGHT(A1,LEN(A1)-FIND("*",SUBSTITUTE(A1,"/","*",LEN(A1)-LEN(SUBSTITUT
E(A1,"/",""))))))