Format Text to Number

[Bruce D.]

I am using excel 2007 and I am trying to create a macro that will convert
account numbers which are stored as text to a number format. I want to get a
record count on how many account numbers there are. Any ideas?

Thanks,

 

[Paul C]

You can use the VALUE function to convert text to numbers, but you don;t even
need to do this to get a count of unique values

This post has a very simple formula that I have used before

http://www.microsoft.com/office/com...5298&catlist=&dglist=&ptlist=&exp=&sloc=en-us

 

[Ron Rosenfeld]

I am using excel 2007 and I am trying to create a macro that will convert
account numbers which are stored as text to a number format. I want to get a
record count on how many account numbers there are. Any ideas?

Thanks,

If there are no duplicates, you can use COUNTA.

If there might be duplicates, you can use this formula which must be
**array-entered**:

=SUM(IF(FREQUENCY(IF(LEN(A1:A1000)>0,MATCH(A1:A1000,A1:A1000,0),""),
IF(LEN(A1:A1000)>0,MATCH(A1:A1000,A1:A1000,0),""))>0,1))
----------------------------------------
To **array-enter** a formula, after entering
the formula into the cell or formula bar, hold down
<ctrl><shift> while hitting <enter>. If you did this
correctly, Excel will place braces {...} around the formula.



Be aware that if you use Paul C's solution (or any solution that involves
changing the text to numbers), you will drop any leading zeros, and any digits
after the first 15 will get changed to a zero.

--ron