Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.

Comma Seperator excel number conver to number

alt_tush
9 - Comet

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. 

20230520_120533.jpg

4 REPLIES 4
binuacs
20 - Arcturus

@alt_tush One way of doing this

binuacs_0-1684569269515.png

 

Yoshiro_Fujimori
15 - Aurora

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

Yoshiro_Fujimori_0-1684647059989.png

 

Output

Yoshiro_Fujimori_1-1684647104874.png

 

apathetichell
18 - Pollux

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.

SeanAdams
17 - Castor
17 - Castor

just to explain what @binuacs 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-jquer....
(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....
(4) Regular expressions - JavaScript | MDN - MDN Web Docs. https://developer.mozilla.org/en-US/docs/Web/JavaScript/Guide/Regular_Expressions.

        

 

Labels