22 Aug 13 8:48 pm
Hey everyone,

I know this is probably a little off topic of this forum, but I would love some help on an excel problem I am having. Here is what I need, and if anyone has the answer I will buy them dinner....ha ha. Well, I will be eternally grateful...hows that.

I need to extract data out of a cell. For example.

http://67.43.164.170:/assets/productima ... -98331.jpg

I need to extract "FMT-98331" from that cell.

Is there a formula that would allow me to do that. I think you can by counting the characters, but I have hundreds of these, and they are not all the same.

Let me know all you excel geniuses!! Maybe I'm just hoping for some magical formula that does not exist.

Jason

I know this is probably a little off topic of this forum, but I would love some help on an excel problem I am having. Here is what I need, and if anyone has the answer I will buy them dinner....ha ha. Well, I will be eternally grateful...hows that.

I need to extract data out of a cell. For example.

http://67.43.164.170:/assets/productima ... -98331.jpg

I need to extract "FMT-98331" from that cell.

Is there a formula that would allow me to do that. I think you can by counting the characters, but I have hundreds of these, and they are not all the same.

Let me know all you excel geniuses!! Maybe I'm just hoping for some magical formula that does not exist.

Jason

22 Aug 13 9:14 pm
Hey Jason,

I thought you'd accidentally linked to a picture instead of a spreadsheet - it took me a moment to realize you were actually supplying the contents of a cell :)

Anyway:

- Do all the cells contain filenames in this format?

(i.e. Are the always a URL, and do they always have a file extension?)

If so, here's the expression you want (assuming the cell contents are in A1):

http://codepad.org/v77f62x8

P.S. there's actually whole Excel forums dedicated to this sort of thing... and failing that, there's always Stack Overflow :) [http://www.stackoverflow.com for the uninitiated]

Edit:

The first two formulas are if you want to/can do it across two cells (this makes for a less scary expression); basically, put those formulas next to each other and replace the 'A1' and 'B1' to point to the correct cells :)

The last one is if you want to do it altogether in one cell... but it's a little scarier :)

Edit 2:

I actually found the first part of the formula here:

http://www.excelforum.com/excel-program ... -find.html

For the technical:

This formula cleverly does some tricks to extract the last part of a string by substituting the last "/" with a phantom character (char 1), then "finding" the position of that phantom character

I thought you'd accidentally linked to a picture instead of a spreadsheet - it took me a moment to realize you were actually supplying the contents of a cell :)

Anyway:

- Do all the cells contain filenames in this format?

(i.e. Are the always a URL, and do they always have a file extension?)

If so, here's the expression you want (assuming the cell contents are in A1):

http://codepad.org/v77f62x8

P.S. there's actually whole Excel forums dedicated to this sort of thing... and failing that, there's always Stack Overflow :) [http://www.stackoverflow.com for the uninitiated]

Edit:

The first two formulas are if you want to/can do it across two cells (this makes for a less scary expression); basically, put those formulas next to each other and replace the 'A1' and 'B1' to point to the correct cells :)

The last one is if you want to do it altogether in one cell... but it's a little scarier :)

Edit 2:

I actually found the first part of the formula here:

http://www.excelforum.com/excel-program ... -find.html

For the technical:

This formula cleverly does some tricks to extract the last part of a string by substituting the last "/" with a phantom character (char 1), then "finding" the position of that phantom character