How to compare numbers in different format?

Help
,
1

Hi,

I have a situation where I want to compare if two numbers are equal. The first number, lets call it A, is from Excel, and the second number, B, is from an invoice system where I use Get text to store it in a string variable.

A = 23500,00
B = 23 500,00

When I compare if they are similar, the check will always fail, because of the extra space (thousand separator) in number B. It should not be a problem to remove the space before comparing the two numbers, but for some reason It does not work using Replace(" ", “”) on number B. The number is unaffected when I write it to log. That has never failed before…

So, do I have to use some alternative method to convert it to string?
Or are there some other methods you guys could suggest so that the two numbers appear in the same format, before I compare them? :slight_smile:

By the way;
Number B is a part of an array, as I had to remove som text before the number (“Sum:23 500”), so I split by “:”. But it is still a string, so it should not have any impact.

2

plz, attach your workflow.

3

Use replace function and you will get what you want.
While compare make sure to check the length of the desired string. Some times it showed as desired string but you will have empty space on it. In that case it will fail to compare.

4

Actually, it is the replace-function which I dont get to work. (I wrote that I used the Trim() function, but that was wrong)

After I split the text, B, it is stored in an array, called splitB. then I use:

splitB(1).Replace(" “,”")

But the output is still 23 500,00 and not 23500,00.

So I still dont know what is going wrong…

5

please follow @Shipra advice

6

Function replaceChars(str As String) As String
'msgbox replacechars (“!@#$%^&*(”) will return !@$%^&()
Dim elem As Variant

replaceChars = str
For Each elem In Array("/", "\", ":", "*", "?", "<", ">", "|", "#", Chr(34))
    replaceChars = Replace(replaceChars, elem, "")
Next

End Function

7

@skandi, Using Regex will be an alternate solution.

Wherever the input is from (Excel/ Invoice System) just use an Assign activity with this Regex Replace option.

For Example :
string A = 23500,00
string B = 23 500,00

Now in Assign follow this,
String AA = System.Text.RegularExpressions.Regex.Replace(String A,“\D”,“”)
String BB = System.Text.RegularExpressions.Regex.Replace(String B,“\D”,“”)

Note:
\D represents the characters other than digits.

Regards,
Dominic :slight_smile:

1 Like
8

I followed @Dominic Regex function and got it to work doing the following:

A = 23 500,00

AA = System.Text.RegularExpressions.Regex.Replace(A,“\D”,“”)

AA = 2350000

This removes all non-digits from the string and thus the “space” gets removed. The “space” has to be something different than a space, since the replace(" ", “”) does not work. I have not encountered this problem before.

Anyways, its working now. The only thing I need to fix is to get back/insert the comma, as it also gets removed during the Regex function. Eventually, I could use the regex function on the Excel sum as well, so that both numbers have the same format (with only digits).

Thanks for the help guys!

9

If you want to avoid the regex (to keep your string variable format for instance), you have to replace the Ascci character 160 (call the “non breaking space”)

→ yourString.replace(Chr(160),“”)

2 Likes