Need numeric value of string containing blanks

[mgallot]

I'm using 8.5 trying to convert a string field into a number so I can use it for calculations. The string field has a length of 10. The string field will contain blanks and commas occasionally. I would like to retain the numeric value of the field and ignore the blanks and commas. I have tried using ToNumber(fld), but it gives me an error that says its not numeric text.

 

[lbass]

You could try something like:

if isnumeric({table.string}) then val({table.string}) else 0

-LB

 

[mgallot]

I actually did try that, but I really don't want to set the value to zero. I want to accept the record even if it has a space or comma. I'm looking for a way to pull the number out of the string. For example:

If my string is 2 000
or 7,000
or 15000

I want to be able to pull the actual number out and perform a calculation.

Thanks.

 

[MJRBIM]

Formula :

ToNumber (Replace ((Replace ({Table.NumberStringField}," " ,"" )),"," ,"" ))

 

[lbass]

Sorry, I misunderstood. MJRBIM's formula should work.

-LB

 

[mgallot]

Gosh, I'm stumped here. The Replace forumula looks like it should work. However I am receiving the same error message that I have gotten with every formula that I have tried in order to coververt this field. Here is the message:

"The string is non-numeric."

This is what the online help says:
The argument to the ToNumber function must be a number stored as a string (for example, a customer number, an ID number, etc.). The string may be preceded by a minus sign and may contain leading and trailing blanks. You have used an argument that is non-numeric and therefore cannot be converted to a number. Change the argument to numeric and recheck.

The Replace formula is changing the argument to numeric, I even tried replacing spaces with zeros and got the same message. The field in the SQL Server table is defined as varchar 10. The record that is currently causing this error message is an empty field. It is not defined as null, but is simply empty.
Thanks for your help everyone.

 

[vidru]

Check to see if it's blank first:

if len(trim({Table.NumberStringField})) = 0 then 0 else
ToNumber (Replace ((Replace ({Table.NumberStringField}," " ,"" )),"," ,"" ))

-dave

 

[mgallot]

Awesome, that did it! I guess I was contradicting myself in that I was checking if the value was null before the Replace, when I already knew that it was not null in the database. Thanks so much everyone.