0

I'm having difficulty using the find() function when in this situation. Say, I have a big text string called "Inventory", which might look like this:

"[some text] 25 Item ABC [more text]"

I want to build a sheet that turns this text string into a proper spreadsheet which looks like this:

|Item A   | 0 |
|Item AB  | 0 |
|Item ABC | 25|

I'm having some success using a combination of MID() and FIND() to pull out the numbers, using this formula:

=IFERROR(NUMBERVALUE(MID([text field],FIND([Item Name],[text field])-4,3)),0)

but I'm getting this instead:

|Item A   | 25|
|Item AB  | 25|
|Item ABC | 25|

This is because "Item A", when searching through the text field, finds "Item A" in "Item ABC". How do I isolate it out such that I'm only returning results for "Item ABC" rather than "Item A" and "Item B" as well?

Versipellis
  • 121
  • 1
  • 1
  • 6

2 Answers2

0

Can you search for an item name with a space after it? ie search for "Item A " instead of "Item A". This relies on a space being used after every item name though.

You formula would become

=IFERROR(NUMBERVALUE(MID([text field],FIND(CONCATENATE([Item Name]," "),[text field])-4,3)),0)

I can't help until you clearly define the text string. I suspect it has characteristics that you could use.

Ideally it should have a delimiting character like a ; or , between ever number and name. Can you change it to have this? This would be the best idea then you can use the suggestion I made above but use ";" instead of " ".

The only other solution will get complicated - you'll need to search for text followed by "a space and one or more numbers". You won't be able to use find for this and would have to use VBA and you don;t want to go there.

HarveyFrench
  • 4,440
  • 4
  • 20
  • 36
  • Unfortunately not. I did consider that, however, the actual Item Name is something like this: "Item ABC DEF G". And the list of items to search for are: "Item ABC", "Item ABC DEF", and "Item ABC DEF G". So one space won't work. – Versipellis Aug 14 '15 at 16:52
  • No way for me to change the text string - I'm running analysis on old datasets. My alternative would be to use something like R to ETL instead. :( – Versipellis Aug 14 '15 at 17:41
  • What does "R to ETL" mean? – HarveyFrench Aug 14 '15 at 17:46
  • [This](http://stackoverflow.com/questions/22542834/how-to-use-regular-expressions-regex-in-microsoft-excel-both-in-cell-and-loops) SO question give information about what you will need to do with VBA. – HarveyFrench Aug 14 '15 at 17:48
0

First, using the find and left functions, drop off everything to the left of item, so your string now starts with "item" and is in a different column. Run Text to columns on that column that has the first result, delimiting by space (record a macro while you're doing it the first time to just run automatically in the future). End result will be the item numbers will be alone in one column.

cyb3rwolf
  • 13
  • 3