XLR8R - - Latest reply:
rizvisa1

- Posts
- 4475
- Registration date
- Thursday January 28, 2010
- Status
- Contributor
- Last seen
- January 6, 2016

Related:

- Excel - cells formatted as numer pesel ???
- Excel cell format not changing ✓ - Forum - Excel
- Excel cell format won't change ✓ - Forum - Excel
- Changing format of date in Excel ✓ - Forum - Excel
- I want to change date format in Excel ✓ - Forum - Excel
- Excel conditional formatting based on date in another cell - How-To - Excel

rizvisa1

- Posts
- 4475
- Registration date
- Thursday January 28, 2010
- Status
- Contributor
- Last seen
- January 6, 2016

ISNA would be true if there is not a match. and I think thats what you are saying that it does. Am I missing some thing here.

For your first problem, you would need to write a custom function to first get the background color. Search the forum, a function to get the background color has been given out many times

For your first problem, you would need to write a custom function to first get the background color. Search the forum, a function to get the background color has been given out many times

I' took the liberty of re-posting my question in a bit more detail today under the heading "comparing a list of names in 2 different lists"

One thing I forget to mention the formula in comparing the 2 lists. I don't think this helps as the bigger problem is in formatting. Here it is anyway :

=ISNA(MATCH('Existng SpareParts'!C17,'NEW SparesParts'!$C$11:$C$32,FALSE))

Thanks for your time.

JOHN

Have you tried the use of an apostrophe, like '000123.

The apostrophe will not be displayed so your data will look the same.

Best regards,

Trowa

This first two paragraphs are directed to Trowa, whilst the others are for Rizvisa1 as a matter of courtesy.

I considered you solution, but it doesn't seem to work for me. For whatever reason my Excel (which is 2003) allows me to put the apostrophe in front of it, but it show exactly that - the apostrophe in front of it. I've had to double click on the mouse and press enter. This gets me of that cell and it appears as a number now. However I have over 4000 lines of data and ca't afford the time to do the same for all of them.

The crazy thing is that a similar thing occured when I tried to work out another way. I tried to change the format to general or even just number (no decimal places), and it behaves in the same manner. It remains exactly as it was before until I double click and enter off the cell. It then changes. I can't do this for all >4000 cells.

Rizvisa1

I played with my data and think I've overcome the problem and thought to share it with you.

As you may have read above, my Excel cells seem to behave badly with the double clicking and pressing enter. I discovered that also when I changed the format of those under the spell of "Numer Pesel"..........anyway.

What I did was converted Data#1 (which is Numer Pesel formatted) to a number format. That remained stable as a number with no leading zeros. My ISNA formula does not work as the Data#2 is formatted as text. This when changed to number, does not remove those zeros in front, and therefore my formula does not compare and report properly. So ...........

I converted Data#1 into number(no decimal places), used LEN formula to work out the remaining number of digits in that data, then used CONCATENATE to add the number of zeros to the front of that number to make a new number 11 digits long (using a nested IF function).

My ISNA then works perfectly well in comparing the two lots of data.

All I had to do was copy and paste these extra columns and job done.

A very weird way of doing I know, but to date my only solution.

I hope this helps anybody else needing to get around this problem and allows you to reflect on a solution to a problem. .... This is why I have not sent samples of data to you to help me.

Am interested to hear what you think.

Thanks everyone.

XLR8R