Unanswered

Find a String within an Excel

(0) Share

Report

Posted on by YARONB

My goal is to classify users to see if they live in USA.

For each user, I have a string that contains their BIO and contains their location.

It can be:

Father, Marketer, Lives in New York.
Or

Agency owner, US

Artist from NY,US.
etc...

Now I want to analyze this text and see if the person actually lives in the US.
But as you can see in the examples not all users claim that they live in US.
Some mentioned USA some only mentioned the city or the state.

What I tried is creating an Excel in which each row contains a city or a state from the US and I've also added two cells one contains the string United States of America and the other one USA.

My next step was to loop in all the Excel cells and see if they appear in the bio of the user.

I wasn't able to get this to work.

I also tried using a dedicated Python script for this but I had many false positive false negative issue.

I don't want to go the nested if route I think it's not efficient.

I'm probably missing something with regard to use of "contain" in the condition or leveraging regex.

But, I don't know what I don't know.

Would love some advice about this.

Categories:

Power Automate Desktop

All responses (3)

Answers (0)

Sort by

Riyaz_riz11 4,219 Super User 2026 Season 2 on at

Like
a
(0)

Report
Copy link

Link copied!

Hi @YARONB ,

Try with below ex, it helps with single condition.

Instead of name you can provide currentItem['column']

If helps mark as solution and if likes make thumbsup

Regards,

Riyaz
Pavel_NaNoi 1,074 on at

Like
a
(0)

Report
Copy link

Link copied!

Unless someone else has a more clever way of doing this, here, try this disgusting piece of regex code I came up with real quick:

(Just CTRL+C all of the text below, and paste this into your automation, it will paste a parse text with the regex, that you can already see, isn't pretty.)

Text.ParseText.RegexParseForFirstOccurrence Text: YourVariableHere TextToFind: $'''(\\bAlabama\\b|\\bAlaska\\b|\\bArizona\\b|\\bArkansas\\b|\\bCalifornia\\b|\\bColorado\\b|\\bConnecticut\\b|\\bDelaware\\b|\\bFlorida\\b|\\bGeorgia\\b|\\bHawaii\\b|\\bIdaho\\b|\\bIllinois\\b|\\bIndiana\\b|\\bIowa\\b|\\bKansas\\b|\\bKentucky\\b|\\bLouisiana\\b|\\bMaine\\b|\\bMaryland\\b|\\bMassachusetts\\b|\\bMichigan\\b|\\bMinnesota\\b|\\bMississippi\\b|\\bMissouri\\b|\\bMontana\\b|\\bNebraska\\b|\\bNevada\\b|\\bNew\\b|\\bHampshire\\b|\\bNew\\b|\\bJersey\\b|\\bNew\\b|\\bMexico\\b|\\bNew\\b|\\bYork\\b|\\bNorth\\b|\\bCarolina\\b|\\bNorth\\b|\\bDakota\\b|\\bOhio\\b|\\bOklahoma\\b|\\bOregon\\b|\\bPennsylvania\\b|\\bRhode\\b|\\bIsland\\b|\\bSouth\\b|\\bCarolina\\b|\\bSouth\\b|\\bDakota\\b|\\bTennessee\\b|\\bTexas\\b|\\bUtah\\b|\\bVermont\\b|\\bVirginia\\b|\\bWashington\\b|\\bWest\\b|\\bVirginia\\b|\\bWisconsin\\b|\\bWyoming\\b|\\bAmerican Samoa\\b|\\bDistrict of Columbia\\b|\\bGuam\\b|\\bNorthern Mariana Islands\\b|\\bPuerto Rico\\b|\\bU\\.S\\. Virgin Islands\\b|\\bUnited States Minor Outlying Islands\\b|\\bUSA\\b|\\bUS\\b)''' StartingPosition: 0 IgnoreCase: False Match=> MatchedState

Lemme know if this is what you're looking for or if I misinterpreted the problem, all this does is basically do an OR statement equivalent in regex for all the American states, as well as "US" and "USA" looking specifically for those words without any other letters around, so Iowa, would work (comma included) but Iowaseyyy wont. Feel free to add any other states to the regex by simply writing |\b*yourstatehere*\b before the end bracket.
Agnius Bartninkas Most Valuable Professional on at

Like
a
(0)

Report
Copy link

Link copied!

If you've got a list in Excel and you're looping through it, you need to check if the string contains the current value from Excel and not the other way around. Make sure you place your operators correctly when using contains, and that you check for one value at a time.

Would be easier to tell you what it is exactly that you're doing incorrectly if you shared a screenshot of your flow, though.

-------------------------------------------------------------------------
If I have answered your question, please mark it as the preferred solution. If you like my response, please give it a Thumbs Up.

I also provide paid consultancy and development services using Power Automate. If you're interested, DM me and we can discuss it.