Regex Syntax and Hlookup Help

[jessy_chow]

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! 

[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_](.+)$

[jessy_chow]

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

Thanks! 

[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 · Accepted solution]

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.

[jrgo · Accepted solution]

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

Thank you! It worked! 

[jessy_chow]

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