Page 1 of 2 12 LastLast
Results 1 to 10 of 13

Convert Numbers To Text Macro

This is a discussion on Convert Numbers To Text Macro within the Excel Questions forums, part of the Question Forums category; I have an excel sheet with a list of numbers that I would like to convert from the number format ...

  1. #1
    Board Regular
    Join Date
    Jun 2009
    Posts
    76

    Default Convert Numbers To Text Macro

    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?

  2. #2
    MrExcel MVP mikerickson's Avatar
    Join Date
    Jan 2007
    Location
    Davis CA
    Posts
    16,481

    Default Re: Convert Numbers To Text Macro

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

  3. #3
    Board Regular MrKowz's Avatar
    Join Date
    Jun 2008
    Location
    St. Louis, MO
    Posts
    6,093

    Default Re: Convert Numbers To Text Macro

    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
    - Posting guidelines, forum rules and terms of use
    - Try searching for your answer first, see how
    - Read the FAQs
    - List of BB codes
    - Please use [CODE] [/CODE] tags when posting your VBA code. It retains spacing, so your code is easier to read, and therefore easier to debug.
    - Please back up your file before using any macros suggested!

  4. #4
    Board Regular
    Join Date
    Jun 2009
    Posts
    76

    Default Re: Convert Numbers To Text Macro

    Worked perfectly, thanks guys!

  5. #5
    New Member
    Join Date
    Mar 2011
    Posts
    4

    Default Re: Convert Numbers To Text Macro

    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 by Burgers; Mar 11th, 2011 at 10:31 AM.

  6. #6
    Board Regular MrKowz's Avatar
    Join Date
    Jun 2008
    Location
    St. Louis, MO
    Posts
    6,093

    Default Re: Convert Numbers To Text Macro

    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.
    - Posting guidelines, forum rules and terms of use
    - Try searching for your answer first, see how
    - Read the FAQs
    - List of BB codes
    - Please use [CODE] [/CODE] tags when posting your VBA code. It retains spacing, so your code is easier to read, and therefore easier to debug.
    - Please back up your file before using any macros suggested!

  7. #7
    New Member
    Join Date
    Mar 2011
    Posts
    4

    Default Re: Convert Numbers To Text Macro

    @MrKowz

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

  8. #8
    New Member
    Join Date
    Mar 2011
    Posts
    4

    Default Re: Convert Numbers To Text Macro

    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

  9. #9
    Board Regular Jasa P's Avatar
    Join Date
    Feb 2012
    Location
    ID
    Posts
    68

    Default Re: Convert Numbers To Text Macro

    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

  10. #10
    MrExcel MVP mikerickson's Avatar
    Join Date
    Jan 2007
    Location
    Davis CA
    Posts
    16,481

    Default Re: Convert Numbers To Text Macro

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

Page 1 of 2 12 LastLast

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com