Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

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

Adding space in between a string ( UK post code) - please help

Antel7
5 - Atom

Hi guys,

 

a quick question, my post code data came to me without spaces in between. 

 

For example: AB101AF, but I want it to be AB10 1AF ( added space after AB10). I tried to use pad-right in formula tool, however not succeeded.

 

Thanks in advance

6 REPLIES 6
DanS
9 - Comet

Hello, 

 

I used a simple regular expression to match the final 3 characters in the field and "replace" with a space character and then the last three characters back into Field1. 

 

FYI - I used the last 3 characters since UK codes always end in the same NAA (numeric, alpha, alpha) - Reference 

 

Note, this does not validate the postal codes. 

 

Thanks,

Dan

 

MarqueeCrew
20 - Arcturus
20 - Arcturus
LEFT([Post Code],4) + ' ' + RIGHT([Post Code],3)

That should do what you are looking for.

 

Cheers,

Mark

 

Alteryx ACE & Top Community Contributor

Chaos reigns within. Repent, reflect and restart. Order shall return.
Please Subscribe to my youTube channel.
DanS
9 - Comet

@MarqueeCrew , I originally was going that route. But after looking up the UK Postal Code formats, it says that they could be any of the following : 

Format (A - alpha, N - numeric)
AN NAA 
ANN NAA 
AAN NAA 
AANN NAA 
ANA NAA 
AANA NAA 

This might not matter to the OP question. But just thought it would help if there were ones that were not in the 4-3 format. 

 

 

MarqueeCrew
20 - Arcturus
20 - Arcturus

@DanS,

 

how about this then?

 

IF
	REGEX_Match([Postal Code], "\u\d\d\u\u")	THEN	Regex_Replace([Postal Code],"(\u\d)(\d\u\u)",'$1 $2')	ELSEIF
	REGEX_Match([Postal Code], "\u\d\d\d\u\u")	THEN	Regex_Replace([Postal Code],"(\u\d\d)(\d\u\u)",'$1 $2')	ELSEIF
	REGEX_Match([Postal Code], "\u\u\d\d\u\u")	THEN	Regex_Replace([Postal Code],"(\u\u\d)(\d\u\u)",'$1 $2')	ELSEIF
	REGEX_Match([Postal Code], "\u\u\d\d\d\u\u")	THEN	Regex_Replace([Postal Code],"(\u\u\d\d)(\d\u\u)",'$1 $2')	ELSEIF
	REGEX_Match([Postal Code], "\u\d\u\d\u\u")	THEN	Regex_Replace([Postal Code],"(\u\d\u)(\d\u\u)",'$1 $2')	ELSEIF
	REGEX_Match([Postal Code], "\u\u\d\u\d\u\u")	THEN	Regex_Replace([Postal Code],"(\u\u\d\u)(\d\u\u)",'$1 $2')	
ELSE [Postal Code]
ENDIF

Capture.png

 

Cheers,

Mark

Alteryx ACE & Top Community Contributor

Chaos reigns within. Repent, reflect and restart. Order shall return.
Please Subscribe to my youTube channel.
Antel7
5 - Atom

Hi DanS,

 

My sincere apologies for the late reply. Many thanks for prompt reply, solution is very concise and beautiful. thanks once again.

 

Best wishes

Ananth

Antel7
5 - Atom

Hi Mark,

 

thank you so much for our help. Your solution is perfectly validate my post code  data and you used regex tools  beautifully. your help is really appreciated.

 

Best wishes

Ananth

 

 

Labels