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?

 

[hpalfrey]

Need some help need to take number data as 12.34
1) remove the decimal and convert to text
2) make sure if it 55 that when I remove the . is comes out as 5500
3) Right align
4) Zero fill to seven digits.

Thanks Heather






Code:


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 S</pre>

 

[jfazzio]

MrKowz,

Can you show me how to change this to work on whatever cells I currently have selected in excel instead of just column a?

 

[MrKowz]

MrKowz,

Can you show me how to change this to work on whatever cells I currently have selected in excel instead of just column a?

Try:

Public Sub FixNumbers()
Dim rng As Range

Application.ScreenUpdating = False

rng.NumberFormat = "@"
For Each rng In Selection
    rng.Value = Format(rng.Value, "00000")
Next rng
 
Application.ScreenUpdating = True
End Sub