Remove numbers and . from string

[craigja]

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

[JosephSerpis · Accepted solution]

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.

[Qiu · Accepted solution]

@craigja 

We can also use RegEx_Replace function

[craigja]

Brilliant thanks folks, just what I needed!

[craigja]

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]

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]

Just stopping by to offer a no regex solution 🙂

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