convert String to Number in worksheets

[halfcoupler]

is there a function for this ?

 

I have a text field in a record format, populated with numbers and I need to multilpicate this with a count field in a worksheet.

And yes, I could convert the field in the record format, but I want to keep this as text field for other entries.

[Tom W.]

Try =Value('<Record Name>'.'<Record Field>')

[Tom W.]

2 hours ago, halfcoupler said:

but I want to keep this as text field for other entries.

 

Hmm if this is in a database + you have non-numerical text in some instances of that field =VALUE will return an error in those cases...

[halfcoupler]

Great! works perfect! Thank you!

 

20 minutes ago, Tom W. said:

Hmm if this is in a database + you have non-numerical text in some instances of that field =VALUE will return an error in those cases...

 

No Problem since I am filtering out all non numeric values by database criteria in the worksheet.

 

[Pat Stanford]

Just to be clear, that non-numerical includes things like unit marks and thousands separators.

 

 So 1000.01 will work in the US.  I believe it will fail in a region using the comma as the decimal point.

 

These will all fail to convert to numbers

1,000.01

1000.01 mm

$1000.01

 

 

[halfcoupler]

Hi Pat, just tested it, it seems the Value function follows the setting in the operating system. Working on a German Windows version, it can handle a value like 43,4868 but it can not handle not 43.4868 .

It fails when having anything else than numbers or the decimal separator in the string.

You are right, this is a buggy solution,- the correct way is to use the appropriate data type in the record format.

 

But I was just looking for a quick & dirty way to do some simple calculations on a few symbols, when there is no other record field available in my record format.

Edited January 18 by halfcoupler

[Pat Stanford]

The results you got were exactly what I expected.

 

It is a good quick and dirty way to get a number.  But only if your data is formatted properly.

 

"Text Mangling" is not worksheet strong point (or Vectorscript for that matter), but if you have a fairly uniform data format we might be able to extract just the numeric part out to use Value on.

 

Something like =Value(Mid(A1, 2, 6)) where A1 contains '€345,67 per meter' would return a number of 345,67.

 

Much harder to do if you don't have a prefix and a length or suffix of fixed lengths or if you need to remove thousands separators inside the number string.

 

Ask If you need help.