Insert or add character into string at intervals

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

Optimization

Accepted answers

benakesh

Hi @ups366 ,

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

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

All comments

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.

Bobe

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

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?