Prev: How do you turn off the clip board option?
Next: Format Text to Number
From: Ron Rosenfeld on 4 May 2010 12:55
On Tue, 4 May 2010 09:02:06 -0700, Bruce D. <BruceD(a)discussions.microsoft.com>
wrote:

>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
First  |  Prev  | 
Pages: 1 2
Prev: How do you turn off the clip board option?
Next: Format Text to Number