From String to Number

[Cfdiaz2103]

Hi Everyone!

I would like to know if there's any way to convert a string data to a number, with all thousand and decimal separators

Example:

Before

1,000,500.23   (String)

After

1,000,500.23  (Number)

The main idea is summarizing some fields in excel from the output in order to validate totals, due to string data can only be counted

Thanks for your response.

[HomesickSurfer]

Hi @apathetichell 

Thx.  Wouldn't my posted solution be cleaner and efficient?  https://community.alteryx.com/t5/Alteryx-Designer-Discussions/From-String-to-Number/m-p/750875#M1801...

[Cfdiaz2103]

Hi @HomesickSurfer !

Well, the thing is I really need both separators (Comma and Point), in order to be summarized.

[HomesickSurfer]

Hi @Cfdiaz2103 

The values, as Fixed Decimal number format can be summed.  If imperative to have the commas, I'm sorry, I don't know how to.

[apathetichell]

@HomesickSurfer I don't know if replace recognizes the non-comma "," as "," and without a data sample I can't test it. Assuming it does - TOTALLY. But if replace recognizes the non-comma I don't get why tonumber() doesn't recognize it.

To clarify the problem is the non-comma which looks like a comma. Any of these solutions would work with real commas.

[HomesickSurfer]

Hi @apathetichell 

I'm using real commas from a text input.  Odd that tonumber() doesn't work.  Workflow attached.

[mdsalmankhan89]

Hi @Cfdiaz2103 , 

Not sure if number data type can be preserved along with the comma separator. 

Probably u can do a work around by using a regex : tonumber(REGEX_Replace([Input], "[^0-9.]", "")) and post summarizing,  format the number with comma separators just before generating the output. 

Thanks 

[apathetichell]

@Cfdiaz2103Can you post some additional data if you haven't been able to get your numbers to convert?

[Cfdiaz2103]

Hi @apathetichell!

Sure.

Example:

The following table contains the Sales for every Client, related to a Customer Segment. Sales is a string field that must be converted to a number, preserving both separators (Comma and point)

Once sales is converted to a number field, I would like to summarize Sales, grouping by Customer Segment. However, comma and point separators must be in.

Since Sales contains string data, it can not be operated arithmetically.

I'd appreciate your response.

[apathetichell]

Why doesn't the multi-field changing it to double work? You'll still have commas and a period. It seems fine on my machine.

The workflow I posted on Friday should convert your data to numbers including both periods and commas.

[apathetichell]

@HomesickSurfer  I guess i had a typo in my test data -  1,23423,23.23 -  It's not a number - that's why it doesn't work. I don't know if @Cfdiaz2103 tried the multi-field formula version it worked fine.