Separating rows from the right on specific character

[David92]

Hi there,

I am working with the following database and i am trying to separate the postcode from the rest of the the address. 

I know i could separate the columns by using 'London' as a delimiter and work from there.

From a skills development point of view I am wondering if it is possible to split it using a right trim using the second space, between London and the first letter of the postcode.  

Also not all the postcodes are the same length in the data set, so i cant split on a certain character. 

Any help would be appreciated greatly.

David

[MarqueeCrew · Accepted solution]

@David92,

The moderators moved your post while I was writing to you.  The bottom line is that I prefer a Regular Expression that looks at the possible values and populates the postal code based upon known PC formats (wikipedia):

Validation[edit]

The format is as follows, where A signifies a letter and 9 a digit:

Format	Coverage	Example
AA9A 9AA	WC postcode area; EC1–EC4, NW1W, SE1P, SW1	EC1A 1BB
A9A 9AA	E1W, N1C, N1P	W1A 0AX
A9 9AA	B, E, G, L, M, N, S, W	M1 1AE
A99 9AA		B33 8TH
AA9 9AA	All other postcodes	CR2 6XH
AA99 9AA		DN55 1PT

The code looks like this as a regular expression:

REGEX_Replace([Address], ".*(\w{2}\d\w\s\d\w{2}|\w\d\w\s\d\w{2}|\w\d\s\d\w{2}|\w{2}\d\s\d\w{2}|\w{2}\d{2}\s\d\w{2})", '$1')

The expression has 5 OR conditions, each separated by a pipe (|) character.

I have included that in the attached workflow.  Here is a picture of some alternative methods, each having it's own merits and cautions.

Cheers,

Mark

[David92 · Accepted solution]

@MarqueeCrew

Thanks for the help!  Appreciate it.

Best

David