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?

 

[taigovinda]

What other database are you importing it into? It's probably easier to do the transform there. Like field1: format(field1,"0000000000") instead of just field1

 

[Rhino373]

I'd guess your database is expecting a number not text. If you see it on your report in Excel with leading zeros then it is text because if it were a number, Excel would drop the zeros.

Basically you insert a column. We'll say the blank column is A and your data is in B. In cell A1 you type =text(B1,0). Fill that formula down the select column A, copy > paste special > values. You can now delete column B. All entries in A should have a little green flag in front of them.

 

[Joe4]

It sounds like your values in Column A are actually numbers with a Custom format being applied. A Custom format does not change the underlying value in the cell, it simply changes how it looks on the Excel spreadsheet.

In order to get it to export with the leading zeroes, your values need to be text. The simplest/best way to handle it is to usually address that in how that column is being populated in the first place (import/enter/write as text instead of numeric). However, if that is not possible, I have a macro that quickly fixes them.

Sub FixColumnA()
 
    Application.ScreenUpdating = False
 
    Dim cell As Range
    
'   Change format of entire column to text
    Columns("A:A").NumberFormat = "@"
    
'   Convert each entry to ten digits
    For Each cell In Range("A1:A" & Cells(Rows.Count, "A").End(xlUp).Row)
        cell = Format(cell, "0000000000")
    Next cell
    
    Application.ScreenUpdating = True
 
End Sub

 

[mirabeau]

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

If this is literally what you want to do, then maybe try (no loop, includes apostrophe)

Sub keepzeros()
Dim a As Range
Range("A1", Cells(Rows.Count, "A").End(xlUp)).Name = "a"
With Range("a").Offset(, Columns.Count - 1)
    .Cells = "=char(39)&rept(char(48),10-len(a))&a"
    Range("a") = .Value
    .ClearContents
End With
End Sub

 

[taigovinda]

If this is literally what you want to do, then maybe try (no loop, includes apostrophe)

Sub keepzeros()
Dim a As Range
Range("A1", Cells(Rows.Count, "A").End(xlUp)).Name = "a"
With Range("a").Offset(, Columns.Count - 1)
    .Cells = "=char(39)&rept(char(48),10-len(a))&a"
    Range("a") = .Value
    .ClearContents
End With
End Sub

Cool! Only thing about this is that lower versions of Excel (like mine) decide that the last column is now 256 and bloats the file size. Maybe just add activesheet.usedrange to the end to accomodate those still living in 2003

 

[cstimart]

Cool! Only thing about this is that lower versions of Excel (like mine) decide that the last column is now 256 and bloats the file size. Maybe just add activesheet.usedrange to the end to accomodate those still living in 2003

I THINK this might work for you, then.

Sub keepzeros() 
Dim a As Range Range("A1", Cells([COLOR=Red]65536[/COLOR], "A").End(xlUp)).Name = "a" 
With Range("a").Offset(, Columns.Count - 1)     
    .Cells = "=char(39)&rept(char(48),10-len(a))&a"     
    Range("a") = .Value     
    .ClearContents 
End With 
End Sub

BTW: Thank You mirabeau!

 

[Joe4]

Out of curiosity, did you try my code?

Usually, you don't need the apostrophe there. All that does is allow you to make a text entry into a General or Numeric formatted cell so that the leading zero is not lost. If you format the cell as Text before entering values into it, there is no need to add the apostrophe so that the leading zero is not lost.

That is what my code does, and it will only run down to your last used cell in column A.

 

[cstimart]

Out of curiosity, did you try my code?

Usually, you don't need the apostrophe there. All that does is allow you to make a text entry into a General or Numeric formatted cell so that the leading zero is not lost. If you format the cell as Text before entering values into it, there is no need to add the apostrophe so that the leading zero is not lost.

That is what my code does, and it will only run down to your last used cell in column A.

Yes, I tried your code, too. Thank you!

I'm still debating which version I will use permanently.

 

[taigovinda]

I THINK this might work for you, then.

Sub keepzeros() 
Dim a As Range Range("A1", Cells([COLOR=red]65536[/COLOR], "A").End(xlUp)).Name = "a" 
With Range("a").Offset(, Columns.Count - 1)     
    .Cells = "=char(39)&rept(char(48),10-len(a))&a"     
    Range("a") = .Value     
    .ClearContents 
End With 
End Sub

BTW: Thank You mirabeau!

The issue that happens on my machine is the code works, but after running it, control+end takes you to the last column of the worksheet, even though the end of the sheet is way farther left than that. Sometimes when you save a file this way, the file size grows because Ecel saves the "contents" of all those empty cells. I'm not sure if this issue still exists after Excel 2003 or not. It is avoided by adding activesheet.usedrange to the end of the code, which makes Excel realize that the last column of the sheet is in fact not in use.