Replace string with strings from lookup table

My problem: I have several thousand article with American units and want to convert them using a comparison list.

Example:
Red Ball 5 inches long
Ball Bearing 1/16 in long
Black Block 1/16 in x 3/8 in
Ball Bearing 11/16Inches long

I have a list like this:
1/16 1,6 mm
1/16 in 1,6 mm
1/16 Inch 1,6 mm
1/16 inch 1,6 mm
11/16 in 17,5 mm
…

I need a macro that compares each column of one table against my conversion table and changes just the exact value.

If I use this macro with xlWhole it does not find anything, if I use xlPart it replaces 11/11 with the 1/16 value. My Lookup table is around 700 entries.

Please Login or Register  to view this content.

I would appreciate any help.

With kind regards, Markus

Code Tags Added
Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code.

Posting code between [CODE]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

Highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found at http://www.excelforum.com/forum-rule...rum-rules.html



(Because you are new to the forum, I have added them for you today. Please take a few minutes to read all Forum Rules and comply in the future.)

So I would take a completely different approach to this problem then you are. I'm not saying what you're trying to do is wrong, just that I think this might be a better approach.

First, I would get rid of the lookup table and just do math directly on each number. That's going to require a little bit of work, but not that much. Here's the process.

1. Save your unconverted strings as a text file.
2. Import that text file into excel (Data -->Get External Data --> From Text)
3. When importing, choose Delimited by space
4. Make sure your fraction column (what will be column C in your example data) is not formatted as general or date (text will do)
5. Your import should look like this:
sdr.png
6. Now do a find and replace for anything in Column C that isn't a number (e.g. Replace "Inches" with "")
7. Format Column C as Number
8. Now run this code:

Please Login or Register  to view this content.

9. Yay, all your conversions are done (you could even set your ConRange to all the data, to pick up things like that 3/8 in.)
10. Format the numbers as you wish.
11. Do a find and replace for things like "in" to "mm"
12. Recombine all the data if you want to by pasting into a word processor and replacing tabs with spaces.


Looks like a lot of steps but I did it in less than a minute. I think that would be faster than 700+ searches even if you could get them set up perfectly. Plus this way, you don't have to keep track of every single conversion you need to do and add it to a conversion table.

I wish you luck then. I'n not sure there will be an easy solution given your data set and approach, but I'd be interested to see it.

If you post what your source data actually looks like (as you suggested it doesn't look like your example Red Ball, etc.), that would probably help a great deal.

Edit: Just FYI, if you are using that lookup table, the entries all need to be ranked because you are using xlPart.

So, for example, you need to list all of the 11/16 conversions before all of the 1/16 conversions, otherwise it will see 11/16 as 1"1/16" and output 1"1,6 mm" which is not what you want.

Every single entry needs to be ranked like this, which for 700+ entries is quite a task.