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.
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
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.
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.