community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
SOLVED

Using Regex to Find String by ASCII Number

Meteor

Hello,

 

I have an example of a problem I'd like to solve which will help me solve similar problems in the future. Say I have a record labeled:

 

<FormulaField expression="[Metric D]/[Metric B]" field="Calc1" size="8" type="Double" /> <FormulaField expression="([Metric C]-[Metric B])/[metric B]" field="Calc2" size="8" type="Double" /> <FormulaField expression="([Metric A]*[Metric A])/([Metric B]-[Metric C])" field="Calc3" size="8" type="Double" />

 

which has so many non-word characters. However I'm looking to count only the number of total appearances of the following non-word characters, ignoring anything else:

 

(

)

*

+

,

-

 

In the ASCII Table (https://www.cs.cmu.edu/~pattis/15-1XX/common/handouts/ascii.html) these values are 40-45. How could I write in the Regex code that I only want to count the characters in this ASCII grouping? This post here (https://community.alteryx.com/t5/Alteryx-Designer-Discussions/Remove-ALL-ascii-Characters/td-p/35381...) regards hexagonal grouping which I can't really finagle to work in this case.

 

File attached (a text input of just the value above).

 

Thanks,

Kieran

 

 

 

Magnetar
Magnetar

Hi,

 

I think the following should work:

 

 

REGEX_CountMatches([Text],'[\x28-\x2d]')

 

 

 

I tested this and a manual method and found 9 matches in each.

 

The key was escaping the hexadecimal value with the "\" character.

 

Hope this helps! 

 

 

EDIT:

Notably, you can also do this (look for a range of characters in ascii) without any hexadecimal values.  The following expression works as well.

 

 

REGEX_CountMatches([Text],'[(--]')

 

Alteryx Certified Partner

How about the following expression that can be placed in a Formula tool:

 

length(REGEX_Replace([Field1],"[^\(\)\*\+\,\-]",""))

 

The RegEx there will replace any character not in the set with nothing, effectively removing all but the characters of interest. Once that is complete, use the length( function to count the characters.

Alteryx Certified Partner
Alteryx Certified Partner

@KieranRidge ,

 

I was going to say:  

REGEX_CountMatches([Text],'[\x28-\x2d]')

But @Claje stole my thunder.

 

Cheers,

 

Mark 

Alteryx ACE & Top Community Contributor

Chaos reigns within. Repent, reflect and reboot. Order shall return.
Nebula

Hi @KieranRidge 

 

There are multiple places(here's one) where you can find ASCII charts to help you map the decimal range (40-45) to the corresponding hexadecimal range (\x28-\x2d), that @Claje (and now @MarqueeCrew) used in their regex formula

 

Dan

Meteor

Thanks, everybody and @Claje has earned the Accept Solution due to being the quickest and giving two different answers that fit the criteria.

Alteryx Certified Partner
Alteryx Certified Partner
A bunch of folks jumped in to help save you. It's ok to skip me, but please do consider recognizing all who took the time to suggest workable solutions for you.

Cheers,

Mark
Alteryx ACE & Top Community Contributor

Chaos reigns within. Repent, reflect and reboot. Order shall return.
Meteor

Apologies - didn't realize I could assign multiple Accept Solutions. Fixing now.

Alteryx Certified Partner
Alteryx Certified Partner
Thanks! Alteryx is always evolving and someone a year from now might just post an easier answer.

Cheers,

Mark
Alteryx ACE & Top Community Contributor

Chaos reigns within. Repent, reflect and reboot. Order shall return.
Labels