Forcing Calculation

[thisandthat]

Hi

I have a cells with a value similar to
1.2.3
And I have some code to turn it into a formula.

Please Login or Register  to view this content.

which gives me: = 1*100+2*10+3
in the cell. The problem is that it doesn't calculate. I guess excel just sees it as text.
I've tried value, clean, trim...past special..Although I'd rather just have a formula that doesn't it rather then having to manually copy and past something.

Thanks

[ChemistB]

Here's a possiblity. It requires an additional cell. Let's say your digits (1.2.3) are in A2, your Concat formula is in B2 and you want your result to be in C2

Select C2, Formulas Tab> Name Manager
Name Eval1
Refers to: = Evaluate(B2)

Then, whereever you have a formula like in B2, to the right of it (i.e. in C2) enter
=Eval1
You should see 123 for your example
Does that work for you?

[thisandthat]

It works, but I've got a list of hundreds of these values, and I'd really rather do it in one cell.

[shg]

How about =--SUBSTITUTE(A1, ".", "")

[shg]

... or just find and replace . with nothing

[thisandthat]

I made a mistake on my origonal code. It should be:

Please Login or Register  to view this content.

So 1.2.3 should come out to 10203, otherwise of course, removing the . would be easy...

[shg]

Replace . with 0