Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

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

Extracting text from string

adombayeva
7 - Meteor

Hello,

 

Could you please advise how to extract the word in " " into separate column using RegEx or other tools:

 

Book - Book Store: "Science" Close gap(s) to Shelf 'Perfect Book' (Look 'Best Composite Book Cost' proxy) per book type / per action

 

Need to have a separate column with Science word

 

Thanks

10 REPLIES 10
jrgo
14 - Magnetar

@adombayeva 

 

Not sure if this would work in every case, but here you go...

image.png

REGEX_REPLACE([Field1], '.*"(.+?)".*', '$1')
adombayeva
7 - Meteor

@jrgo 

 

Thanks for your help, it worked well. But now I revealed that few texts have more than one " "

Thus picks up the second or third word in  " " , instead of first word in " " only and another issue is that one text doesn't have " " at all, and requires to be left empty.

 

 

Book: "Science T" Use of 13-5/8" tjk vs 14" tjk

Legend Lights: WxW contingency reduction

 

Thanks in advance

 

jrgo
14 - Magnetar

@adombayeva  This should solve for that.

 

image.png

IF REGEX_REPLACE([Field1], '.*?"(.+?)".*', '$1') = [Field1]
THEN NULL() //use '' instead of NULL() if empty is preferred
ELSE REGEX_REPLACE([Field1], '.*?"(.+?)".*', '$1')
ENDIF

 

adombayeva
7 - Meteor

@jrgo 

 

It worked well for the text without " ", but for this text it turned to empty as well:

 

Book - BK-3: "Science EE" Use of shelf during searching and Look at the instructions with correct label-string

 

Thanks

 

jrgo
14 - Magnetar

Hmm...

I just tested that text and it extracted as expected. What version of Alteryx are you running?

image.png

adombayeva
7 - Meteor

Today I updated it to 2019.3 version

 

Cheers

jrgo
14 - Magnetar

Run the attached workbook and let me know if it does extract correctly or not on your end.

 

If it does, the source you're reading from may be using the formatted/pretty double quotes (what you'd see in a Word document) in some of the text strings. 

adombayeva
7 - Meteor

@jrgo 

 

Your file is worked:

 

clipboard_image_0.png

The source is macro-enabled. Can we try another formula with RegEx?

 

Thanks 

 

 

adombayeva
7 - Meteor

@jrgo 

 

I tried to add Multi-Row Formula with expression: Replace([Field1]," ” ",' " ') before Formula tool with Regex_replace expression and finally get rid out that empty.

 

Thanks for relentless support, 

Have a nice weekends,

Cheers

Labels