Extract text from a file string

Meredith Rhodes

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

Paul Newcome

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?

Scott Peters

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.

Meredith Rhodes

https://community.smartsheet.com/discussion/comment/397486#Comment_397486

@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

@Scott Peters - whoa. I'll see if I can get this to work. I knew it was figure-out-able.

Meredith Rhodes

@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

Scott Peters

@Meredith Rhodes - Glancing quickly, the highlighted text looks out of place, and should be a (

Paul Newcome

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

Meredith Rhodes

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

Paul Newcome

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

Meredith Rhodes

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

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

Paul Newcome

@Meredith Rhodes Yes. Sorry about that. My keyboard has been acting up a bit today. Glad you were able to get it sorted.