Extract text from a file string
Hello!
Simple question (I hope).
I have a list of document locations - that look like this:
_Central Documents/Training/Investigators/Rhodes, Meredith/CV
I would like to parse 'Investigators' into a staff role column, 'Rhodes, Meredith' into a contact column, and 'CV' into a document type column.
What is the basic formula to get this done?
Thank you!
Meredith
Meredith Rhodes, PhD
ClinicalTrials.gov Specialist
UW School of Medicine & Public Health
UW Clinical Trials Institute
mkrhodes@clinicaltrials.wisc.edu
Answers
-
Will it always be the same structure in that "Investigators" will ALWAYS be the third piece of the string, name always comes after that, cv always comes after name, and it is always forward slashes as delimiters?
-
Hi @Meredith Rhodes - I find that adding helper columns aid the process of working out formulas like these. Those helper columns can be hidden, but this can also be done without helper columns. To Paul's point, if the structure (# of slashes in the string) will be consistent, then this should work for you. If the # of slashes (levels of the directory) will change, you may need something different.
-
@Paul Newcome "Investigators" will always be the third piece of the string (although, it won't always be "Investigators" - but other roles), the name always comes after that, and the document type after that, always with forward slashes as delimiters.
Meredith Rhodes, PhD
ClinicalTrials.gov Specialist
UW School of Medicine & Public Health
UW Clinical Trials Institute
mkrhodes@clinicaltrials.wisc.edu
-
@Scott Peters - whoa. I'll see if I can get this to work. I knew it was figure-out-able.
Meredith Rhodes, PhD
ClinicalTrials.gov Specialist
UW School of Medicine & Public Health
UW Clinical Trials Institute
mkrhodes@clinicaltrials.wisc.edu
-
@Scott Peters - I wonder if you can confirm this formula for me? This is what I (think I) see on your image in the 'Investigators' column for the formula without a helper column
(to extract "Investigators" out of this string: _Central Documents/Training/Investigators/Rhodes, Meredith/CV):
=LEFT(REPLACE(REPLACE, 1, FIND("/", [file string]@row, 1), ""), 1, FIND("/", REPLACE([file string]@row, 1, FIND("/", [file string]@row, 1), "")), ""), FIND("/", REPLACE(REPLACE([file string]@row, 1, FIND("/", [file string]@row, 1, ""), 1, FIND("/", REPLACE([file string]@row, 1 FIND("/", [file string]@row, 1), "")), "")) - 1)
I'm getting an unparseable error, can you highlight what might be causing grief?
Thank you!
Meredith
Meredith Rhodes, PhD
ClinicalTrials.gov Specialist
UW School of Medicine & Public Health
UW Clinical Trials Institute
mkrhodes@clinicaltrials.wisc.edu
-
-
Try these:
=MID([Column Name]@row, FIND("!", SUBSTITUTE([Column Name]@row, "/", "!", 2) + 1, FIND("!", SUBSTITUTE([Column Name]@row, "/", "!", 3) - (FIND("!", SUBSTITUTE([Column Name]@row, "/", "!", 2) + 1))
=MID([Column Name]@row, FIND("!", SUBSTITUTE([Column Name]@row, "/", "!", 3) + 1, FIND("!", SUBSTITUTE([Column Name]@row, "/", "!", 4) - (FIND("!", SUBSTITUTE([Column Name]@row, "/", "!", 3) + 1))
=RIGHT([Column Name]@row, LEN([Column [Name]@row) - FIND("!", SUBSTITUTE([Column Name]@row, "/", "!", 4))
-
Thanks, @Paul Newcome
I am able to get the last formula to work to return the document type.
The first two formulas are returning an Incorrect Argument Set error - any ideas about how to address this error?
I appreciate your help!
Meredith
Meredith Rhodes, PhD
ClinicalTrials.gov Specialist
UW School of Medicine & Public Health
UW Clinical Trials Institute
mkrhodes@clinicaltrials.wisc.edu
-
Yes. Correct my missed parenthesis. Haha. Sorry about that. Try these instead:
=MID([Column Name]@row, FIND("!", SUBSTITUTE([Column Name]@row, "/", "!", 2)) + 1, FIND("!", SUBSTITUTE([Column Name]@row, "/", "!", 3)) - (FIND("!", SUBSTITUTE([Column Name]@row, "/", "!", 2)) + 1))
=MID([Column Name]@row, FIND("!", SUBSTITUTE([Column Name]@row, "/", "!", 32)) + 1, FIND("!", SUBSTITUTE([Column Name]@row, "/", "!", 4)) - (FIND("!", SUBSTITUTE([Column Name]@row, "/", "!", 3)) + 1))
-
Thank you @Paul Newcome!
What would I need to change about that last formula to return the person's name? Right now it is returning various lengths of "_Central Documents..."
Also - I'm super curious if Smartsheet offers some version of a 'build crazy formulas' class? Or is this understanding only gained by playing with formulas for a living :)
Thank you again for this help, invaluable.
Meredith
Meredith Rhodes, PhD
ClinicalTrials.gov Specialist
UW School of Medicine & Public Health
UW Clinical Trials Institute
mkrhodes@clinicaltrials.wisc.edu
-
@Paul Newcome - I figured it out!
=MID([Column Name]@row, FIND("!", SUBSTITUTE([Column Name]@row, "/", "!", 32)) + 1, FIND("!", SUBSTITUTE([Column Name]@row, "/", "!", 4)) - (FIND("!", SUBSTITUTE([Column Name]@row, "/", "!", 3)) + 1))
32 should be 3.
Meredith Rhodes, PhD
ClinicalTrials.gov Specialist
UW School of Medicine & Public Health
UW Clinical Trials Institute
mkrhodes@clinicaltrials.wisc.edu
-
@Meredith Rhodes Yes. Sorry about that. My keyboard has been acting up a bit today. Glad you were able to get it sorted.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.8K Get Help
- 434 Global Discussions
- 138 Industry Talk
- 470 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 65 Community Job Board
- 486 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!
