Convert Numbers To Text Macro

Draperies

Board Regular
Joined
Jun 29, 2009
Messages
79
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:

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?
 

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type
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.

Code:
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
 
Upvote 0
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!
 
Last edited:
Upvote 0
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)

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 Sub

If at any point you want to stop the macro, just press ESC and it will force-break the macro.
 
Upvote 0
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
 
Upvote 0
Hi, sorry for disturbing you. I'm looking for code that can convert number to text.

For example
photostream
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
 
Upvote 0

Forum statistics

Threads
1,213,487
Messages
6,113,937
Members
448,534
Latest member
benefuexx

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top