Extract Text with variation in "key words" from different cell

Adriane Price

Hello,

I am attempting to extract a variety of words sometimes in different lengths to another cell for consistency on reports, if possible. Not sure if I am overthinking this or making a 'newbie' mistake, definitely I am not a formulas guru but usually if I tinker long enough I figure it out. I am stumped. If it is not possible via formula's then that is fine, would a mapping/data mesh work in this scenario?

Otherwise, I can bag it for now and my colleague can keep doing this manually as there is no rush or expectations. Curious at this point only.

Example:

Second Column

Avamar - Administration --> extract Avamar

NetWorker - Administration --> extract NetWorker

Data Domain - Administration --> extract Data Domain

VNX - Project Management --> extract VNX

First Column

Global Alliance --> extract Global Alliance

SDS - Canada --> extract Canada

SD - Consulting Cloud IT transformation - South --> extract Consulting Cloud IT transformation - South

Consulting - Federal --> extract Consulting

IDS - South --> extract IDS

The below formulas I have tried to use to no avail:

=RIGHT(Region@row, LEN(Region@row) - FIND("*", Region@row))

=MID(Region@row, 6, 8) but as you can see for Federal because there is no SDS in front of it cuts off.

=RIGHT(Region@row, 4)

=LEFT(Region@row, 4)

=MID(Region@row, 6, 2) + RIGHT(Region@row, 4)

Sam M.

@Adriane Price

Something else that might help is this example formula:

=IF(CONTAINS("West", Test@row), "West", IF(AND(CONTAINS("Canada", Test@row), NOT(CONTAINS("Hello", Test@row))), "Canada", IF(CONTAINS("Central", Test@row), "Central", IF(CONTAINS("East", Test@row), "East", IF(CONTAINS("South", Test@row), "South")))))

You can search for another word or a word you do not want. In the image something that contains "hello" with "Canada" it will not display "Canada" because it contains "hello".

IF(AND(CONTAINS("Canada", Test@row), NOT(CONTAINS("Hello", Test@row))), "Canada")

Sam M.

Hi Adriane,

Maybe you can try IF with CONTAINS.

This is a mini example:

=IF(CONTAINS("Avamar", Skill@row), "Avamar", IF(CONTAINS("NetWorker", Skill@row), "NetWorker"))

You can keep on adding IFs for the quantity of words you need.

Hope it helps,

Sam

Sam M.

I did some more testing:

The formula I used in Extract 1 (Blue) is:

=IF(CONTAINS("Avamar", Skill@row), "Avamar", IF(CONTAINS("NetWorker", Skill@row), "NetWorker", IF(CONTAINS("Data Domain", Skill@row), "Data Domain", IF(CONTAINS("VNX", Skill@row), "VNX"))))

The formula I used in Extract 2 (purple) is:

=IF(CONTAINS("Global Alliance", Test@row), "Global Alliance", IF(CONTAINS("Canada", Test@row), "Canada", IF(CONTAINS("Consulting Cloud", Test@row), "Consulting Cloud IT transformation - South", IF(CONTAINS("Consulting", Test@row), "Consulting", IF(CONTAINS("IDS", Test@row), "IDS")))))

In the second formula I put "Consulting Cloud" before "Consulting" so consulting doesn't dominate everything that has the word consulting and you can categorize correctly.

Adriane Price

@Sam M. - I like the concept however I have hundreds of skills to go through, what I provided was just a sample of the few. Regions I have about 14 so that may be do-able.

Sam M.

@Adriane Price I see. Another thing I can think of is having another sheet with all the skills in a column and another column with the the word you want to provide, with that you can use an INDEX/MATCH. The creation of the sheet might take time but at the same time it might help with this sheet.This is all that I can think of right now.

There might be better answers!

I hope you find what you are looking for,

Sam

Adriane Price

For the "Region" (first) column the formula worked except for consulting it still pulled in 'Federal' however there will be many others that state Consulting - West, or Consulting - Northeast, or Consulting South...etc same with IDS but it looks 100x better.

=IF(CONTAINS("Global Alliance", Region@row), "Global Alliance", IF(CONTAINS("Canada", Region@row), "Canada", IF(CONTAINS("Consulting Cloud", Region@row), "Consulting Cloud IT transformation - South", IF(CONTAINS("Central", Region@row), "Central", IF(CONTAINS("Federal", Region@row), "Federal", IF(CONTAINS("West", Region@row), "West", IF(CONTAINS("Northeast", Region@row), "Northeast", IF(CONTAINS("CI", Region@row), "Converged", IF(CONTAINS("Consulting", Region@row), "Consulting", IF(CONTAINS("South", Region@row), "South", IF(CONTAINS("IDS", Region@row), "IDS")))))))))))

Adriane Price

@Sam M. - oh yeah I can try that too okay, I will give that a go. Thank you so much for your time and guidance!

Sam M.

@Adriane Price

From your formula:

=IF(CONTAINS("Global Alliance", Region@row), "Global Alliance", IF(CONTAINS("Canada", Region@row), "Canada", IF(CONTAINS("Consulting Cloud", Region@row), "Consulting Cloud IT transformation - South", IF(CONTAINS("Central", Region@row), "Central", IF(CONTAINS("Federal", Region@row), "Federal", IF(CONTAINS("West", Region@row), "West", IF(CONTAINS("Northeast", Region@row), "Northeast", IF(CONTAINS("CI", Region@row), "Converged", IF(CONTAINS("Consulting", Region@row), "Consulting", IF(CONTAINS("South", Region@row), "South", IF(CONTAINS("IDS", Region@row), "IDS")))))))))))

Consulting - Federal has federal also, since the IF for "federal" is before "consulting" then the whole formula will stop there without checking the "consulting" IF.

Maybe you can try moving the IF: (I haven't tested this formula yet, might work)

=IF(CONTAINS("Global Alliance", Region@row), "Global Alliance", IF(CONTAINS("Canada", Region@row), "Canada", IF(CONTAINS("Consulting Cloud", Region@row), "Consulting Cloud IT transformation - South", IF(CONTAINS("Central", Region@row), "Central",IF(CONTAINS("Consulting", Region@row), "Consulting" , IF(CONTAINS("West", Region@row), "West", IF(CONTAINS("Northeast", Region@row), "Northeast", IF(CONTAINS("CI", Region@row), "Converged",IF(CONTAINS("Federal", Region@row), "Federal", IF(CONTAINS("South", Region@row), "South", IF(CONTAINS("IDS", Region@row), "IDS")))))))))))

Or adding more specifics like the "Consulting Cloud" IF.

Sam M.

@Adriane Price you're welcome!

Sam M.

@Adriane Price

Something else that might help is this example formula:

=IF(CONTAINS("West", Test@row), "West", IF(AND(CONTAINS("Canada", Test@row), NOT(CONTAINS("Hello", Test@row))), "Canada", IF(CONTAINS("Central", Test@row), "Central", IF(CONTAINS("East", Test@row), "East", IF(CONTAINS("South", Test@row), "South")))))

You can search for another word or a word you do not want. In the image something that contains "hello" with "Canada" it will not display "Canada" because it contains "hello".

IF(AND(CONTAINS("Canada", Test@row), NOT(CONTAINS("Hello", Test@row))), "Canada")