Previous Topic Next Topic

EXCEL - Cells formatted as NUMER PESEL ???

Solved/Closed
XLR8R - Jul 1, 2011 at 07:09 PM
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 - Jul 5, 2011 at 04:50 PM
Hello,


Firstly, just a quick one:
I want to use conditional formatting to check if a cell ( say A1) has a red background and if it does then report "yes" or "red" into the next cell (say B1).



Secondly, I am comparing to columns of data using :
=ISNA(MATCH(A45,$B$10:$B$5902,FALSE)) but striking probables as it shows an incorrect TRUE (which is not a match). The cell A45 does in fact have a match in B10:B5902, but the cell format is CATEGORY: Special TYPE: Numer Pesel LOCALE: Polish
My number is 11 digits long and has leading zero's. Because of the above cell format it appears it doesn't compare right. I've tried changing the format to NUMBERS,GENERAL, or CUSTOM (for 11 zero's), but it always reverts back to this "Numer Pesel" ???

I've tried using the LEFT command to chop of one of the leading zero's as the format can be changed to GENERAL or NUMBER and not revert back. This leads to another problem where
the LEFT command could change some numbers without a leading zero and therefore appears that it exists as the remaining numbers are the same except for that leading zero.

How do I get rid of Numer Pesel.

Your help would be appreciated.
Related:

2 responses

Answer 1 / 2
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
Jul 2, 2011 at 08:32 AM
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
0
XLR8R
Jul 2, 2011 at 10:24 AM
Yes ISNA tells me what is NOT a match. I then sort the TRUE and FALSE to gather all those unmatched numbers for dealing with later. My problem really is the format : "number PESEL".
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
0
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 555
Jul 4, 2011 at 08:54 AM
Hi 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
0
XLR8R
Jul 5, 2011 at 09:29 AM
Hi Trowa & Rizvisa 1
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
0
Answer 2 / 2
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
Jul 5, 2011 at 04:50 PM
Thanks for your courtesy of replying.

What if you dont use left but rather multiply by 1. I think that would drop of any leading 0 and would not have impact on the value either.
0

Similar discussions

Date formatting in dd/mm/yyyy
nisha -
appu -

11 responses
How to calculate positions (1st,2nd,3rd...) in Excel
hardy -
TrowaD -

6 responses