Comma Seperator excel number conver to number

Question

Hi,

I have exce file where there is a column which format is number.

Number are populated in this column in comma separators format.

I want to convert it proper number format without any seperator in value.

Also negative values are populated in (234.56) in excel. So i dont want this brackets too.

How do i achieve this.

You can refer attached email which help to understand input data and my expected output.

Thank your in advance.

SeanAdams · Answer

just to explain what @binu_acs posted, since Regex can be scary for anyone seeing it for the first time.

Basically - your question is asking how to take numbers out of (xxx,xxx.xx) or xxx,xx.xx and remove the brackets and the commas.

There are 2 ways to do this:

a) just do a simple replace of ( with a -; ) with nothing; and comma with nothing.   Depending on your data, that may be the easiest way.

b) use RegEx to work with patterns- and for this I'd strongly recommend using either Chat GPT/Bing Chat - or RegEx101.com.   Your goal is to become self-sufficient - and ChatGPT and Bing Chat are a great way to learn different techniques like this.

RegEx is REgular Expression - it's a language that you can use to parse data and cleanup data - it's very very common.

Regex is also fairly quirky to use - so I'd recommend asking ChatGPT or Bing Chat for guidance.

Here's the post from BingChat:

Question: regular expression that can be used to remove commas and brackets from numbers in the format of ($xxx,xxx,xxx.xx):

Answer:

Here is a regular expression that can be used to remove commas and brackets from numbers in the format of ($xxx,xxx,xxx.xx):

```
\$\d{1,3}(,\d{3})*\.\d{2}
```

This regular expression matches the following pattern:

- `$`: matches the dollar sign.
- `\d{1,3}`: matches one to three digits.
- `(,\d{3})*`: matches zero or more occurrences of a comma followed by three digits.
- `\.`: matches the decimal point.
- `\d{2}`: matches two digits.

You can use this regular expression with a string replace function in your programming language of choice to remove commas and brackets from numbers in this format.

I hope this helps!

Source: Conversation with Bing, 6/1/2023
(1) Comma Separated Numbers Regex - Stack Overflow. https://stackoverflow.com/questions/16620980/comma-separated-numbers-regex.
(2) Format Number based on Format String in JavaScript/jQuery. https://stackoverflow.com/questions/3224152/format-number-based-on-format-string-in-javascript-jquery.
(3) How can I define a Regex to remove [xx]xxxxx[arch] xxx from a string .... https://stackoverflow.com/questions/58539751/how-can-i-define-a-regex-to-remove-xxxxxxxarch-xxx-from-a-string.
(4) Regular expressions - JavaScript | MDN - MDN Web Docs. https://developer.mozilla.org/en-US/docs/Web/JavaScript/Guide/Regular_Expressions.

apathetichell · Answer

if this is a number column in excel and your delimiter preference matches between excel and Alteryx this should be automatically read in as a number column in Alteryx. you may see a comma or not see a comma at your option using the settings in your browse tool - this does not change the underlying data type.

Yoshiro_Fujimori · Answer

Hi @alt_tush ,

Another way is to use ReplaceChar() function.

I understand RegEx takes more resources than exact matching.

So I try to avoid using it when the data volume is big.

Workflow

Output

ToNumber.yxmd