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

[Antel7]

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

[DanS · Accepted solution]

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]

LEFT([Post Code],4) + ' ' + RIGHT([Post Code],3)

That should do what you are looking for.

Cheers,

Mark

[DanS]

@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 · Accepted solution]

@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

Cheers,

Mark

[Antel7]

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]

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