In case you missed the announcement: Alteryx One is here, and so is the Spring Release! Learn more about these new and exciting releases here!

Alteryx Designer Desktop Discussions

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

Remove numbers and . from string

craigja
10 - Fireball

Hi,

 

I have a number of fund names which have a number and full stop in front of them as follows:

 

1. My personal fund

23. Johns Pension Pot

301. Mavis Savings

 

I want to get the following:

My personal fund

Johns Pension Pot

Mavis Savings

6 REPLIES 6
JosephSerpis
17 - Castor
17 - Castor

Hi @craigja you can use Regex replace. I've mocked up a workflow let me know what you think? The syntax \d+\.\s will look for a digit one or more followed by a period and then a white space and replace it with nothing in the field.

 

Regex_Replace_130421.JPG

Qiu
21 - Polaris
21 - Polaris

@craigja 

We can also use RegEx_Replace function

Capture1A.PNG

craigja
10 - Fireball

Brilliant thanks folks, just what I needed!

craigja
10 - Fireball

Sorry jumped too quick with that as a solution!  It only removed 1 number - there might be 1, 2 or 3 numbers then the .

 

Ive added in data cleansing to remove any numbers but Im sure there is a more elegant way with regex!

 

Craig

apathetichell
19 - Altair

Hi!

 

Quick question - are you saying 1, 2 or 3 numbers or 1,2 or 3 digits? The formula should remove all digits unless there

are commas then [9876543210,]+ would work in place of \d

 

Here are two formulas you can try:

REGEX_Replace([fund name],".*\.\s(.*)","$1")

REGEX_Replace([fund name],"[9876543210,\s]+\.\s(.*)","$1")

 

 

The top one apparently gets rid of everything with trailing periods (ie two numbers or anything with trailing periods) - the second one will not work with two numbers ending with periods.

 

Both of these will have problems if you have periods in your fund names. - so if that's the case, if you could post more detailed examples of your data it would help.

AdamR_AYX
Alteryx Alumni (Retired)

Just stopping by to offer a no regex solution 🙂

 

Substring([Field1], FindString([Field1], ". ") + 2 )

Adam Riley
https://www.linkedin.com/in/adriley/
Labels
Top Solution Authors