Split string fields with spaces from the right

[chvizda]

Hi all

I have a field which contains streetnames and housenumbers. I need to put the house numbers into a sperate field.

Unfortunately sometimes the streets have 2 words with a space in between. So my thoughts are to start the split from the right side and not from the left. Because the house number is everytime after the last space (from left) or before the first space (from the right).

Example:

Hillstreet 2

Upper Hillstreet 234

Very Up Hillstreet 66a

Top of Hillstreet 667

...

Is there a formula or a regex possible to split the housenumber based on the space before ?

Many thx in advance

Steffen

[MarqueeCrew · Accepted solution]

@chvizda,

If you can count on the pattern looking like your sample data, then an expression could be written that takes the first group of data (before a digit) and puts that into the first field and then takes the second group into another field.  Note:  I would drop the space before the digits in the process.

Here's the Expression:

(.*)\s(\d+.*)

Hopefully, this solves your challenge.

Cheers,

Mark

This workflow is saved in version 11.3.

[chvizda]

Thanks that works perfect

Steffen

[Joe_Mako]

Another option to get the last word in a string with Regex is:

(\S+)$

This will capture the last consecutive non-white-space characters in the string.

If you wanted to ensure there is at least one white-space character in the string, eg not capturing anything when there is only a single word:

\s(\S+)$

[Cerys]

I realise this is an old question but it came up when I was searching for a solution to this problem today. I found another solution which uses a single formula:

ReverseString(GetWord(ReverseString([Name]),0))

This works if you want the last word, and the string is separated by spaces. Basically it's 3 steps, nested:

ReverseString([Name]) - reverses the whole string

GetWord(<string>, 0) - this gets the first word of the string

ReverseString - reverse the word back to normal