Airtable Sync integration: Google Drive - File Path split into separate fields

[Maria]

Hi 

This is a File Path from Google Drive integration.

BOOK 📘/CHAPTER/PAGE/PARAGRAPH/SENTANCE/WORD

I am trying to create formulas to split the File Path into separate fields for each folder. I can get the name of folder and the parent folder but want to separate it all out so I can see subfolders etc. I have been trying variations of LEFT/RIGHT/MID and LEN but can't quite work it out.

Any help much appreciated.

Thanks, Maria

[TheTimeSavingCo]

I've built an extension which does exactly this, and you can find it on the Airtable marketplace here

For the formula side of things, I don't think you need RIGHT or MID.  With LEFT and LEN you should be able to get the first split of text, and then you would use `SUBSTITUTE()` to replace said first split of text with nothing, and then use the same LEFT and LEN logic, does that make sense?

[Maria]

Thanks Adam. I had a look at your extension but not sure it would work for me at this time.

Can you clarify your formula suggestion. Are you saying to extract from the left (start)? As I need it to extract from the end.

My thinking behind the formula is.

I have the formula to create - the name name and the parent folder - /SENTENCE/WORD

I have been trying to extract that field from the file path to reduce the file path as then I could extract up to 1st / and then work my way through any middle bits. But I have to reduce file path from right so sub folders appear in correct field/place. I can get name and parent folder in separate fields but just can't work out how to remove from file path? Which is why I was playing with all options.

Your help is really appreciated!

[TheTimeSavingCo]

Hm, if your desired end goal is to have one field per split text, and so with reference to:
`BOOK 📘/CHAPTER/PAGE/PARAGRAPH/SENTANCE/WORD`, you'd end up with 6 fields each with one word in it, then the extension would do that.  Is that not what you're trying to do?

Assuming my understanding of what you're trying to do is right, then, yeap, using LEFT() and SUBSTITUTE() would work fine, you'd just work left to right.  Could you try using LEFT and LEN to get the first split of text, and then you using `SUBSTITUTE()` to replace said first split of text with nothing, and then use the same LEFT and LEN logic, to get the second split and so on and let me know what issues you face?