Find number in a string

I am trying to use the Regex tool to find a word and a series of numbers.

The column I am working with is a string column that is a "comment" column, meaning any text or numbers can be entered into the column. I need to locate the word "Cluster" and then pull the 2 - 4 numbers after that. The numbers will have a period. Here are some examples...

Cluster 2.3

Cluster 11.1

Cluster 11.10

There may be text before or after the word "Cluster".

After I find that, I need the first 1 - 2 numbers.

I know this is possible with the regex tool. I just don't know enough regex to write it. Thanks in advance.

Regex

Accepted answers

Thableaus

Hi @FreeRangeDingo

You may try this REGEX.

REGEX_Replace([Field], ".*Cluster.*?(\d{1,2}).*", "$1")

Cheers,

All comments

Thableaus

Hi @FreeRangeDingo

You may try this REGEX.

REGEX_Replace([Field], ".*Cluster.*?(\d{1,2}).*", "$1")

Cheers,

FreeRangeDingo

With a filter or two applied before the expression, I can make this work. Can you help me understand the syntax?

REGEX_Replace([perfstatus comment], ".*Cluster.*?(\d{1,2}).*", "$1")

.* -- means zero or more of any character; I assume this is there to indicate that there could be text or characters before or after the word cluster.

? -- means once or none

\d -- is matching a digit (as opposed to a non digit).

\d{1,2} -- means 1 to 2 digits (or 1 to 2 times) before the period

(\d{1,2}).* -- I think the parenthesis are grouping, but I don't know why they are needed.

$ -- means end of string, but I don't know what "$1" means.

Thanks for the response!

Quick Links

This months top contributors

AkimasaKajitani 387

mceleavey 383

mbarone 337

Hollingsworth 335

LanisC 335