Insert or add character into string at intervals

[Bobe]

Situation: Strings that are 4 characters long are getting concatenated into one sting in a single column due to insufficient controls during data input. For example: 'ABCDwxyz' should instead be split into 'ABCD' and 'wxyz' 

Sometimes 2 strings are concatenated, sometimes more. For example, 4 strings concatenated might look like this: 'ABCDwxyzLMNOefgh'

Using the following function (followed by a Text to Column tool, using a space as the delimiter) allows me to correct for this, but seems really crude:

IF 
[Site Len] = 8
THEN Substring([No 5th char],0, 4) + " " + Substring([No 5th char],4,4)

ELSEIF 
[Site Len] = 12
THEN Substring([No 5th char],0, 4) + " " + Substring([No 5th char],4,4) + " " + Substring([No 5th char],8,4)

ELSEIF 
[Site Len] = 16
THEN Substring([No 5th char],0, 4) + " " + Substring([No 5th char],4,4) + " " + Substring([No 5th char],8,4) + " " + Substring([No 5th char],12,4)

ELSE 'Error'

ENDIF

I imagine there's a more elegant solution out there. Any ideas? 

Here's a depiction of the input and desired output: 

Input	Output1	Output2	Output3	Output4
ABCD	ABCD
ABCDwxyz	ABCD	wxyz
ABCDwxyzLMNOefgh	ABCD	wxyz	LMNO	efgh

[john_miller9]

Hi @Bobe,

Could you provide an example of the input data and desired output?  That would go a long way in helping the community help you with optimizing this workflow.

jm

[Bobe]

@john_miller9 Certainly. I've added a mock up to the bottom of the original post. Hopefully that helps illustrate the concept. 

[KaneG]

If it's 4 characters each time then Regex Tokenize with the Expression '(.{4})' will get you what you want.

[rahul1011]

Try this: (In reg exp, you can also write .{4})

[tylancaster]

Apologies if I'm not doing this right - my first post.  My issue seems similar here, but I'm trying to do something very basic.

I have a string that outputs " 17'4.5" " with no space between the feet and inches.  I would like to add a space between the apostrophe and the 4.5 in the string so that it reads " 17' 4.5" ".  A slight change, but it's pretty important when looked at among thousands of records.  Any help with Regex in doing this?

Thanks in advance - Tim.

[benakesh]

Hi @tylancaster ,

You can use

1) text to columns and concatenate  or

2)  regex replace  to add a space between feet and inches. 

[ups366]

Hi @benakesh 

Thank you for your share, but i have another question: if a string have many ' in text, how can we replace all at once ?

Source; 17'4.568'25

Expression: REGEX_Replace([Field1], "(.\d+')(.*)", '$1 $2')

Output: 17' 4.568'25    (only the first replace success, but not all)

[benakesh · Accepted solution]

Hi @ups366 ,

String function replace can replace  '  with  ' and  a space.

Replace([Field1], "'", "' ")  

[tylancaster]

Benakesh- thank you, this is perfect.  Thank you for your efforts!!