Formatting raw data via VBA

Posted on 2011-10-14
Last Modified: 2012-05-12

I have the above data which gets dumped from a website by one of our users. However, due to formatting issues, the macro setup I designed for her is not working. I've pin pointed one of the format issues to be above.

Condition is

Card # are:
- Always 5 digits (including leading zeros)

I told her to use text version, such that she is putting '00023 in everywhere (eg) but the dumped data sometimes has this wierd stuff as above (no leading zeros and the format being in 'General' instead of text)

This is being an issue throughout the workbook. Is there a prebuilt function in VBA to change all above to text format and format it to 5 digits (fill in leading zeros)?

Any start-up help is appreciated and I can continue!

I want to design a button on top which would feed all values on the specific row to this function and paste it back as text.

Question by:Shanan212
    LVL 44

    Expert Comment

    by:Martin Liss
    MyVar = Format$(CardNo, "00000")
    LVL 12

    Expert Comment

    You could assign this macro to a button.  It formats column A so all the numbers are five characters.
    Sub Macro1()
    Columns("A").NumberFormat = "00000"
    End Sub

    Open in new window

    LVL 33

    Accepted Solution

    Do you just want to convert all the Card # to 5 digits.

    You could use a worksheet formula.

    =TEXT(VALUE(A2), "00000")

    Or code:
    For I = 2 To Range("A" & Rows.Count).End(xlUp).Row
       With Range("A" & I)
          .NumberFormat = "@"
          .Value = Format(Val(Range("A" & I)), "00000")
       End With
    Next I

    Open in new window

    LVL 13

    Author Comment

    I designed a quick function to test your code but it just pasted same stuff

    Sub hifunc()
    Dim changer As String, sheet1 As Worksheet
        Set sh1 = Worksheets("Sheet1")
        Dim n As Integer
        n = 1
        Do Until n = 5
            changer = Format$((sh1.Cells(n, 1).Value), "00000")
            sh1.Cells(n, 2).Value = changer
            n = n + 1
    End Sub

    Open in new window

    Data I used is

    LVL 13

    Author Closing Comment

    That is exactly what I was looking at! Thanks!

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Maximize Your Threat Intelligence Reporting

    Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

    I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
    This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
    This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
    This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

    779 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    15 Experts available now in Live!

    Get 1:1 Help Now