Number to Text

[cstimart]

I have a couple reports that Column A contains 100's of 10 digit numbers, however, some are 8 or 9 due to the leading zero(s). I am able to show the number with the leading zero, but when I import into a different database, the leading zero(s) are not recognized (and need to be).

Using a macro, how can I quickly convert 100's of cells to text so the hold the zero(s) with the leading apostrophe?

 

[mirabeau]

Hi,

It occurred to me that if some data are negative, you mightn't like the way my earlier code handles this.

So if some data are possibly negative, you may prefer the modified version

Sub keepzeros2()
Dim a
Range("A1", Cells(Rows.Count, "A").End(xlUp)).Name = "a"
With Range("a").Offset(, ActiveSheet.UsedRange.Columns.Count)
     .Cells = "=char(39) & if(a>0,char(32),char(45)) &" & _
            "rept(char(48),10-len(abs(a))) & abs(a)"
    Range("a") = .Value
    .ClearContents
End With
End Sub

This code is value-based rather than format-based, hence the leading apostrophe. Whichever type code is used should depend on the user's preferences.

But the one above allows the use of repeated formula-based data entries into columnA, whereas it seems not all format-based codes will do this without some modification.



taigovinda,

Thanks for your constructive comments. Your suggestion is included in the modified code above.

Several other workarounds available if that were a problem. One of which is making the code looping.

 

[taigovinda]

mirabeau,

Thanks for the nod on that one - I wouldn't always go around mentioning the used range issue except that before someone on here showed me the fix for it, I spent months using 50+ MB files that, once fixed, were only 1MB...

Nice non-looping approach to this problem.

Tai