Alteryx Designer

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

Insert or add character into string at intervals

Highlighted
7 - Meteor

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: 

InputOutput1Output2Output3Output4
ABCDABCD   
ABCDwxyzABCDwxyz  
ABCDwxyzLMNOefghABCDwxyzLMNOefgh
Highlighted
Alteryx Partner

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

Highlighted
7 - Meteor

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

Highlighted
Alteryx
Alteryx

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

Highlighted
8 - Asteroid

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

String.JPG

Highlighted
5 - Atom

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.

Highlighted
12 - Quasar

Hi @tylancaster ,

You can use

1) text to columns and concatenate  or

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

 

Highlighted
8 - Asteroid

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)

Highlighted
12 - Quasar

Hi @ups366 ,

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

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

Highlighted
5 - Atom

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

Labels