Regex Syntax and Hlookup Help

Hello,

I am looking for help for 2 things.

1. How do I pull the column of data based on a specific criteria in a cell in that column?

Ex - pulling the column information if row 5 has "603500 Tools" in the cell

2. In Column A - I would like to pull out the characters that are after the first underscore and then pull out the characters that are in the third part of the text.

I have tried various regex syntax but due to the string of text in Column A being inconsistent in the format (i.e, some with two underscores, some with a space), I'm not able to come up with a syntax that captures all the various scenerio's.

I tried to find the various styles of format in the string of text below and shown what I want as an output.

Original	Desired Output1
CC_15005_MANAGER CUSTOMER SUPPORT	15005	Manager Customer Support
PC_P0187_DIRECTORY DISTRIBUTION	P0187	Directory Distribution
IM_0021 TECH DIV (1014)	0021	Tech Div (1014)
PC_P0056 INTERNET TRANSITTING FEES	P0056	Internet Transitting Fees

Hopefully this all makes sense. I have attached the excel file. FULLBUD_IS_T_NETY with Name (2) is the raw file that I am working with.

Thanks for your help!

Alteryx Help - Regex and Hlookup.xlsx

Designer Cloud

Regex

Accepted answers

KGT

I take this as 2 problems, one is to split the entity, the other is to reference the column.

I've shown a couple different ways. If you don't want to change the column names, then the only way I can think of is to Transpose and then filter to get the column name then join that, but it could get super complicated if there is anything weird (also not good on performance). Better would be to structure the data correctly and then use that, hence that's what I've shown.

Regex and HLookup.yxmd

jrgo

@jessy_chow see if this gets you closer to what you need. Note that i copied the data in your Excel file into a Text Input tool, so when connecting your Excel file, be sure to set/check the input option "First Row Contains Data".

The hlookup logic is similar to @KGT solution, which pivots and concatenates to create new headers. I then use a Find Replace tool to match against your HLOOKUP list. Filter only keeps what matched and the F1 column (since TOTAL ENTITY wasn't in the lookup list). Dynamic Rename to rename the columns and then a Dynamic Select tool to remove columns that still have a F# name.

The REGEX solution is what i had responded with yesterday.

jessy_chow.yxmd

All comments

jrgo

Hi @jessy_chow

Answering #2 first... this expression used in the RegEx tool should split the string up for you.

^(.+?)_(.+?)[_ ](.+)$

For your first question, assuming you're not trying to filter records out and only retain the data if the string search matches, I'd suggest using a Formula tool with an IF statement with a nested CONTAINS() function, like

IF CONTAINS([TOTAL ENTITY], '603500 Tools')
THEN [VALUE OR COLUMN TO RETURN]
ELSE NULL()
ENDIF

If you are trying to filter the records, then just use the "Contains" operator.

Hope this helps!

-Jimmy

flying008

Hi, @jessy_chow

Q1: Could you please upload sample data as you want like Q2.

Q2: use RegEx tool to parse by :

Spoiler

^([[:alpha:]]+)_([[:alnum:]]+)[\s_](.+)$

录制_2025_07_10_09_24_44_311.gif

jessy_chow

I have reuploaded the file with the desired output for Q1.

Thanks!

Alteryx Help - Regex and Hlookup.xlsx

jessy_chow

The row that I am trying to search for has no "Name".

Total Entity is Starts on Row5. However the accounts I am trying to filter on is on Row4. So unfortunately, the formula doesn't work.

flying008

Hi, @jessy_chow

Sorry, I can't get your logic.

KGT

I take this as 2 problems, one is to split the entity, the other is to reference the column.

Regex and HLookup.yxmd

jrgo

The REGEX solution is what i had responded with yesterday.

jessy_chow.yxmd

jessy_chow

Thank you! It worked!

jessy_chow

Thanks a bunch for your help! I ran it and it worked!

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703