community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
Announcement | We'll be doing maintenance between 9am-11am MT on Saturday, January 25th, which may impact your experience. Thanks for your patience as we work on improving the community!
SOLVED

Extracting text from string

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

Alteryx Certified Partner

@adombayeva 

 

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

image.png

REGEX_REPLACE([Field1], '.*"(.+?)".*', '$1')
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

 

Alteryx Certified Partner

@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

 

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

 

Alteryx Certified Partner

Hmm...

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

image.png

Meteor

Today I updated it to 2019.3 version

 

Cheers

Alteryx Certified Partner

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. 

Meteor

@jrgo 

 

Your file is worked:

 

clipboard_image_0.png

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

 

Thanks 

 

 

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