I'm a programmer with lots of languages, but somehow I've never learned Excel macro or formula language. I'm sure there must be a simple way to do this, but I'm stumped.
I have a column of file names that look like this:
/home/www/shoppermarketexpo/dot/uploads/10DOT_33A_1275_1308304857_1.jpg
/home/www/shoppermarketexpo/dot/uploads/10DOT_15G_1184_1.jpg
/home/www/shoppermarketexpo/dot/uploads/10DOT_5A_1450_1.jpg
/home/www/shoppermarketexpo/dot/uploads/10DOT_18A_1341_1308325044_2.jpg
What I need to do is return just the file name, split into several parts based on the underscore. I'm fine if this takes multiple steps - i.e. formula 1 returns a column with 10DOT_33A_1275_1308304857
, then formula 2 uses that result to return a second column with 10DOT_33A_1275
and so on. I was hoping there'd be a function similar to PHP's explode, where you can take a string and return an array based on a delimiter (in this case, the underscore.) I'm guessing that isn't possible in formula; I'd be OK with VBA, although I'm on a Mac and don't know if it's available to me.
I know about MID, LEFT, and RIGHT, but all of them depend on you knowing the number of characters you want to return, and in this case I don't. It varies from row to row; the only criteria that works is splitting the file name into multiple columns at the underscores.
Is this possible?