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
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.

@Scott Peters  whoa. I'll see if I can get this to work. I knew it was figureoutable.

@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  Glancing quickly, the highlighted text looks out of place, and should be a (

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

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

@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 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
Check out the Formula Handbook template!