Missing Negative number when extracting data from text format

[SH_94]

Hi Community, 

Currently i had extracted the data from the txt format. However, i noted that negative amount will be not read in this case. When i click the text to column tool as per screenshot below, it will appear the negative number as per screenshot below.

However, it is surprisingly disappear when i click the select tools as per screenshot below.

From the configuration view of select as per screenshot below , it seems like i was unable to adjust the size of doable and would like to know how to deal with it when we have this issue.

I have also attached the workflow below for your reference.

Thank you so much for the help.

[apathetichell]

Hi,

One quick thing - you are keeping this datatype as a text type but you are running tonumber() on it. If you're trying to convert it to a number you need to change the datatype. Can't say that this is the reason for your error but your tonumber() function is in the part of your workflow generating the error.

[AngelosPachis]

@SH_94  in your replacechar function, you have to add a comma in the first part of the statement as I have done in my previous response, so

IF Contains([Field1], "(") THEN -Tonumber(ReplaceChar([Field1],"(),", ""))
ELSE Tonumber([Field1])
ENDIF

Also, if you look at the initial solution of this thread, I have checked one of the checkboxes in the configuration window of the multi-field formula tool that changes the data type from a string to a double (I have highlighted that in yellow). In your screenshot, it doesn't seem like this option is enabled as @apathetichell  noticed.

Can you please try changing your expression this time and let me know if works? 🙂

[apathetichell]

@AngelosPachisInterestingly if you have commas in localization under user settings you can keep the ","s... just tested on my system and one of these is a string and one of these is a double...

Screengrab to illustrate...

 

[AngelosPachis]

Interesting @apathetichell ,

Do your user settings look something like this?

EDIT : I tried using your formula with a Tonumber function but left the data type as a string. Oddly, the tool didn't give me any error, but the commas were removed because of that  Tonumber function 

[apathetichell]

@AngelosPachis   - yeah for the most part (miles vs kilometers and different fonts).  My system also will not force decimals on fixed with/fixed with (ie. 19.2 fixed with on 10.50 will look like 10.5 - not 10.50)... does your system require the commas to be removed for the tonumber() conversion? Always good to find out these little quirks and intricacies.

for another random datapoint - I posted the screengrab above because when I cut and pasted the values from a browse tool into the community post the commas were omitted so the point I was trying to illustrate was rendered moot.

[SH_94]

Hi @AngelosPachis and @apathetichell ,

Thanks a lot for the guidance.

I just checked the box and it pop out error as below:

My initial data format used is Vstring for this particular column. Will this cause the error to appear?

Many thanks again for your help.

[AngelosPachis]

@SH_94 can you please confirm that after your "Else" statement, you are using a Tonumber() function please? 

It would also help if you send over some mock data based on what you have at your disposal + that multi0field formula tool, so it would be easier to troubleshoot

[SH_94]

Hi @AngelosPachis ,

The screenshot below is the formula that i have inputted.

Currently my workflow run smoothly and no error if i don't check the option: change output data type to. It is kind of weird but it works.

Apart from the first formula above, i also build the second formula as per screenshot below:

May i know if it is possible to combine both formula together?Basically the first formula i want to get rid of bracket & coma and replace it with negative sign. While for the second formula, i want to get rid of coma for positive figure.

Hence, would like to know if we can combine both formula together.

many thanks for your help.

[apathetichell]

@SH_94 

If you want to convert to a number - the formula has to have tonumber([_currentfield_]) in the else clause. Try that change and then re-running it.