Convert Numbers To Text Macro

[Draperies]

I have an excel sheet with a list of numbers that I would like to convert from the number format to text format where all the numbers are 5 characters and preceded by "0"s. In excel I am able to use the =TEXT(A2,"00000") function to achieve the desired results... so for example the number 2 would become "00002" and 847 would become "00847". However, I cannot achieve the same results in my macro with the following code:

LRow = ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Row
i = 2
Do While i <= LRow
    Cells(i, 1).Value = WorksheetFunction.Text(Cells(i, 1).Text, "00000")
    i = i + 1
Loop

This simply produces the same values and even format in the cells it works on. Does anyone have any suggestions on how to handle this?

 

[mikerickson]

Instead of WorksheetFunction.Text you could use the VBA function Format.

 

[MrKowz]

You need to format the column as TEXT before inputting the values, otherwise using the .Value method will automatically change any numerical string to a numerical value, thus trimming off the 0s.

Public Sub FixNumbers()
Dim i   As Long, _
    LR  As Long
 
LR = Range("A" & Rows.Count).End(xlUp).row
Range("A1:A" & LR).NumberFormat = "@"
Application.ScreenUpdating = False
For i = 1 To LR
    Cells(i, 1).Value = Format(Cells(i, 1).Value, "00000")
Next i
Application.ScreenUpdating = True
End Sub

 

[Draperies]

Worked perfectly, thanks guys!

 

[Burgers]

I have the exact same issue (except need 7 digits instead of 5), When i run this macro it converts the first cell to text perfectly. However, when it tries to move to the next cell (A3) my excel sheet freezes up and i have to force close every time. Using excel 2010 and I adjusted the macro to include a different name, description, 7 0's, and a ctrl- shortcut

Sub ConvertID()
'
' ConvertID Macro
' Convert ID# to text
'
' Keyboard Shortcut: Ctrl+q

Dim i As Long, _
LR As Long

LR = Range("A" & Rows.Count).End(xlUp).Row
Range("A1:A" & LR).NumberFormat = "@"
Application.ScreenUpdating = False
For i = 1 To LR
Cells(i, 1).Value = Format(Cells(i, 1).Value, "0000000")
Next i
Application.ScreenUpdating = True
End Sub

EDIT: Never mind, i had another excel sheet open on my other monitor with the same ctrl-shortcut. Great macro works perfectly. thanks!

 

[MrKowz]

Burgers,

Are you sure that Excel is freezing? With the Application.ScreenUpdating lines, it will make Excel appear to be frozen, so that the macro can run significantly faster. Try this adjusted code so you can monitor the progress of the macro in the status bar (bottom left corner of Excel)

Sub ConvertID()
'
' ConvertID Macro
' Convert ID# to text
'
' Keyboard Shortcut: Ctrl+q
Dim i   As Long, _
    LR  As Long
LR = Range("A" & Rows.Count).End(xlUp).row
Range("A1:A" & LR).NumberFormat = "@"
Application.ScreenUpdating = False
For i = 1 To LR
    Application.StatusBar = "Currently on row " & i & " of " & LR
    Cells(i, 1).Value = Format(Cells(i, 1).Value, "0000000")
Next i
Application.ScreenUpdating = True
Application.StatusBar = False
End Sub

If at any point you want to stop the macro, just press ESC and it will force-break the macro.

 

[Burgers]

@MrKowz

See Edit at bottom of my last post. It was my error, Macro works perfectly. Thanks!

 

[Burgers]

Piggy backing on the original question:

How would I get the macro to sort column B after it has converted all the numbers to text.

Thanks

 

[Jasa P]

Hi, sorry for disturbing you. I'm looking for code that can convert number to text.

For example

http://www.flickr.com/photos/7727011...in/photostream
1. I have this number 8000 (this number is in costume format) in a cell
2. This number has links to the source files
3. I need this number becomes "Delapan Ribu Rupiah" (this is Bahasa language)

I'm using Excel 2007, Windows XP for Home PC and Vista for Notebook

Any help would be great
Best Regards,


Jasa

 

[mikerickson]

I don't know Bahasa, but this might be adapted.
http://support.microsoft.com/kb/213360