Scan Spreadsheet, Convert Two Values Into One

**themurph2000** · 08-17-2012, 09:10 AM

OK, I have a very ugly example, but getting the people who designed the spreadsheet to change it is not an option.

I have a list of products that are created at several plants. The people who handed me this spreadsheet place the amount on hand in one row (on the same row as the product). On the row just below that, they may or may not have a value which is the number they already shipped. What I need to do is instantly replace both numbers with the difference between the two. Oh, and as an added bonus, I have to strip letters from the ends of the numbers of in-stock products. (I can use the left function for that, I'm sure.)

Here's an example attachment. Anybody have any ideas?sample-work.xls

**mike7952** · 08-17-2012, 09:57 AM

I dont know of any formulas that would do what your wanting. But a Macro can, This should work for you.

sample-work1.xls

**themurph2000** · 08-17-2012, 10:12 AM

It works great, except I gave the wrong data to work with. The numbers in those first rows (with a letter attached) could begin OR end with a letter. (Something like "H2400N " is a possibility.) I apologize.

**mike7952** · 08-17-2012, 10:44 AM

Ok no problem Replace the code with this. It remove any thing thats not 0-9. So It Could Be N1V101A output would be 1101

Please Login or Register  to view this content.

**themurph2000** · 08-17-2012, 11:02 AM

OK, cool. I was trying to do what you just did, which is use the function I had to remove the letters from the numbers and combine it with your step-through of subtracting the values. Of course, yours actually worked.

Thanks so much!