We have extended our Early Bird Tickets for Inspire 2023! Discounted pricing goes until February 24th. Save your spot!

Alteryx Designer Discussions

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

Regex Replace Formula- Removing Ending Digit

garretwalters12
8 - Asteroid

Having trouble with my regex replace. If there is a digit ending in Column 1 I want it removed unless the ending of Column 1 is FY\d\d.

 

My current formula is. RegexReplace([Column 1],"(.*|.*FY\d\d$)(\d)","$1") but no luck because the result of Column 1 Row 1 is showing " Estimated Quarter Monetized in FY20 or FY2"

 

 

Estimated Quarter Monetized in FY20 or FY21Estimated Quarter Monetized in FY20 or FY21
Rate Upside or Downside2Rate Upside or Downside
Rate Value (in millions)2Rate Value (in millions)
Estimated Quarter Monetized in FY20 or FY215Estimated Quarter Monetized in FY20 or FY21

 

Thanks!

7 REPLIES 7
Thableaus
17 - Castor
17 - Castor

Hi @garretwalters12 

 

Getting a tip from Adam today.

 

You could try something without regex.

 

IF Left(Right([Column1], 4), 2) = "FY"

THEN [Column1]

ELSE TrimRight([Column1], "0123456789") ENDIF

 

Cheers,

Thableaus
17 - Castor
17 - Castor

@garretwalters12 


Using REGEX.

 

IF REGEX_Match([Column1], ".*FY\d{2}") THEN [Column1]
ELSE REGEX_Replace([Column1], "(^.*?)\d+$", "$1") ENDIF

 

Cheers,

garretwalters12
8 - Asteroid

This does not provide the wanted output. The Output produces: "Estimated Quarter Monetized in FY20 or FY"

 

Estimated Quarter Monetized in FY20 or FY215Estimated Quarter Monetized in FY20 or FY21
jdunkerley79
ACE Emeritus
ACE Emeritus

Based on what sent:

REGEX_Replace([Field1], "((?<!FY\d)\d)$", "")

should do what you want.

 

The (?<!FY\d) is a negative look-behind so if part of the FY string it won't match.

The $ at the end makes it match then end of the string

 

Sample attached

alexlong1117
5 - Atom

Please try this:

 

REGEX_Replace([Field1], "(.*FY\d{2}|.*[^\d])(\d*)", "$1")

Thableaus
17 - Castor
17 - Castor

@garretwalters12 

 

I'm sorry I didn't see you could have 3 digits at the end.

What about this?

 

EDIT: Now I get it.

Remove the "+" sign at the second part of the expression.

 

IF REGEX_Match([Column1], ".*FY\d{2}") THEN [Column1]
ELSE REGEX_Replace([Column1], "(^.*?)\d$", "$1") ENDIF

 

Cheers,

Billibo418
5 - Atom

Hi,

 

I'm searching for a similar REGEX that removes the letter(s)/number(s) at the end of a string only if the digit is the same than the second group of the string.

 

Exemple :

 

100 10 RUE DES SAPINS RIMOUSKI 10

22 BOUL PRAIRIES QUEBEC

34 4 AV DE LA MOUTARDE 4

22 PO BOX 44

122 B 10E RUE CHIBAUGAMEAU B

 

Result :

 

Remove the 10 at the end of the first line

Remove the 4 at the end if the 3rd line

Remove the B at the end of the 4th line

 

I'm new with Alteryx and it could help me so much.

 

Thank you in advance ✌️

 

Labels