Just want to chat? Talk some smack. If it doesn't fit into one of the areas below then you'll be posting here (Note: Please try and find the best section before posting here)

Excel Formula Question

PremiumMember
sierracommerceco
Posts: 113
Joined: 19 Jul 13
Trust:

Excel Formula Question

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
  • 0
Site Admin
jonathan_l
Posts: 121
Joined: 15 Nov 10
Trust:
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
  • 1
PremiumMember
sierracommerceco
Posts: 113
Joined: 19 Jul 13
Trust:
You are a genius!!

Probably would have been better off with excel forum, but I trust you guys and you are a lot quicker in the response.

Thanks again!!

Jason
  • 0
Site Admin
jonathan_l
Posts: 121
Joined: 15 Nov 10
Trust:
Haha, well Affilorama didn't hire me for my looks ^_^

Anyway, hope it works out; if there's any cells in your spreadsheet which don't work with this formula, post them here and I should be able to adapt it :)
  • 0
PremiumMember
sierracommerceco
Posts: 113
Joined: 19 Jul 13
Trust:
Thanks for the help. You guys are the bomb!!

Jason
  • 1

This topic was started on Aug 22, 2013 and has been closed due to inactivity. If you want to discuss this topic further, please create a new forum topic.

cron