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

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.

mrshl9898

Well-known Member
Joined
Feb 6, 2012
Messages
1,485
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,113,780
Messages
5,544,190
Members
410,597
Latest member
Jasen79
Top