Extract Text with variation in "key words" from different cell
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)
Adriane
Best Answer
-
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")
Answers
-
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
-
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.
-
@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.
Adriane
-
@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
-
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
-
@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!
Adriane
-
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.
-
@Adriane Price you're welcome!
-
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")
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.8K Get Help
- 376 Global Discussions
- 207 Industry Talk
- 440 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 284 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!