Cell formula

ds_robb

Board Regular
Joined
Aug 25, 2002
Messages
71
I am writing a pivot table trying to count the number of unique values in a range of cells, but noticed I got different numbers when I loaded the same data to PowerBI. I believe the issue is some of the numeric data in the cell range are being stored as text, so it's counting certain values twice (once as a numeric type, once as a text type). I've tried converting all the cells to Numeric by format cells but it still seems to keep the text type ones as text. Below you can see the numeric fields are aligned right and the text aligned left.

Is there a way to

a) either truly convert these fields to numeric, or
b) in the original data sheet there was a green warning triangle on the text cells, which if clicked gave you the option to fix. Can you do this enmass with hundreds of cell rather than looking at each one. I don't want to ignore the error, I want to resolve it.

Hope this makes sense.

8004263​
8004263
8001120​
8001120
 

Some videos you may like

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

mrshl9898

Well-known Member
Joined
Feb 6, 2012
Messages
1,488
You could add a new column, =NUMBERVALUE(A2) (or whatever cell the numbers in), and leave it there or replace the original after converting it
 

Watch MrExcel Video

Forum statistics

Threads
1,114,520
Messages
5,548,529
Members
410,845
Latest member
OldSwimmer1650
Top