Excel won't recognize cell value as a number

**Fabienne88** · 01-22-2013, 09:35 AM

Hi everyone,

I have a column of numbers as a result of a formula (=SI(ESTNUM(P4);P4;DROITE(P4;NBCAR(P4)-1))) and before that, P4 is a result of the VLOOKUP formula. Now I want to compare this result (numeric value) with another cell (numeric value) but excel doesn't recognize these cell as numbers. When I try to compare the cells (A1=A2 e.g.), he says #VALUE. I have already tried formatting the cell format to 'number' and I have also tried the VALUE function but that doesn't work either..

Your help/advice would be greatly appreciated!

**daddylonglegs** · 01-22-2013, 09:50 AM

The result of RIGHT function (DROITE) is always text - try adding 0 to force to numeric e.g.

=SI(ESTNUM(P4+0);P4+0;DROITE(P4;NBCAR(P4)-1)+0)

**Norie** · 01-22-2013, 09:53 AM

How did you use VALUE?

**Fabienne88** · 01-22-2013, 10:06 AM

Daddylonglegs, this formula works except for some cells which then also shows #VALUE. I added the excel file so it is clear.

**Norie** · 01-22-2013, 10:22 AM

There's a strange character (ASCII code 29) at the end of each string where you are getting !VALUE#.

You can remove it by using CLEAN in either the formula in column H, or perhaps a little easier in the VLOOKUP in column G.

Formula:

Please Login or Register  to view this content.

**Fabienne88** · 01-22-2013, 10:40 AM

@Norie: That works!

Thank you so much for you help Norie and Daddylonglegs!

Excel won't recognize cell value as a number

LinkBack

Thread Tools

Rate This Thread

Display

Excel won't recognize cell value as a number

Re: Excel won't recognize cell value as a number

Re: Excel won't recognize cell value as a number

Re: Excel won't recognize cell value as a number

Re: Excel won't recognize cell value as a number

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions