ALTERYX INSPIRE | Join us this May for for a multi-day virtual analytics + data science experience like no other! Register Now

Alteryx Designer Discussions

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

Insert or add character into string at intervals

Bobe
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
john_miller9
11 - Bolide

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
7 - Meteor

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

KaneG
Alteryx
Alteryx

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

rahul1011
8 - Asteroid

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

String.JPG

tylancaster
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.

benakesh
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. 

 

ups366
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)

benakesh
12 - Quasar

Hi @ups366 ,

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

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

tylancaster
5 - Atom

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

Labels