community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx designer Discussions

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

Split string fields with spaces from the right

Highlighted
Asteroid

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

Alteryx Certified Partner
Alteryx Certified Partner

@vizda,

 

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+.*)

Capture.png

Hopefully, this solves your challenge.

 

Cheers,

Mark

 

This workflow is saved in version 11.3.

Alteryx ACE & Top Community Contributor

Chaos reigns within. Repent, reflect and reboot. Order shall return.
Asteroid

Thanks that works perfect

 

Steffen

Quasar
Quasar

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+)$

Meteoroid

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

 

Labels