Extracting text from string

[adombayeva]

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

[jrgo · Accepted solution]

@adombayeva 

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

REGEX_REPLACE([Field1], '.*"(.+?)".*', '$1')

[adombayeva]

@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]

@adombayeva  This should solve for that.

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

[adombayeva]

@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]

Hmm...

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

[adombayeva]

Today I updated it to 2019.3 version

Cheers

[jrgo]

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]

@jrgo 

Your file is worked:

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

Thanks 

[adombayeva]

@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