Identify cells with scientific formula and replace with original data.

[[email protected]]

Good day, gurus.

Before I started working on the VBA for my little project I wanted to get an idea of if it were possible.

I often have to review a listing of several thousand OE numbers, and compare to data in another workbook. I compare by performing a vlookup and pulling in data from one workbook, and then compare using a formula in the next column: "=if(A=B,"","X")" Because I am doing a vlookup the format of the cells must match, text or numbers.

In the attached sample workbook you will see I have highlighted several numbers that if I perform a text to columns to convert the data to numbers then they show scientific formulas instead. Is there a way to filter a long list of numbers like this to show only the cells that are scientific formulas?

The way I plan to do the lookup is that I would first copy and paste the OE numbers to a new column. I would then perform a text to columns on that column to convert the data to numbers, and would then replace the cells that contain scientific formulas with the original number and format from the original column. Is this even possible?

Thanks in advance for any help you can offer.

[AB33]

I have changed all data in column B in to text, using a Text to column. I have not copied them in to new column I do not know where the original data is. Perhaps, I do not understand your request. See sheet 1, column B

[[email protected]]

Hello, AB. I don't believe I did a good job describing my problem. If I have a column of numbers similar to those in Column A of my original workbook, only many more numbers, and I do a Text to Columns on that row I get the results in column B of my original workbook. What I am hoping to do is sort the data in Column B so it only shows cells that contain the "scientific data error", and replace the data in those cells with my original data from Column A.

I think I can do a majority of the code, though I will likely post for some help, but at this point I am just trying to determine if there is even a way to identify the cells that contain the "scientific data error".

Hope that clarifies things a bit.

[AB33]

You can run a loop to identify if a cell is formatted scientific, but this is not reliable as you can see from this sample code. Some of the rows are formatted as text.

Please Login or Register  to view this content.

[[email protected]]

you are correct. It doesn't look like that will work for me. Is there a way to filter to show only the cells that are formatted scientific?

[AB33]

The code bolds cells which are formatted as scientific. If you run the code, some of the cells are bold. The formatting type is down to excel and hence this method is not reliable.

[[email protected]]

That's what I found when I ran the code AB. Some cells were set to bold, but some of them were not, hence unreliable. I will fiddle with things a bit and see if I can find a work around. Thanks for your time and effort.