Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium


Formatting raw data via VBA

Posted on 2011-10-14
Medium Priority
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 50

Expert Comment

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

Expert Comment

ID: 36969761
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 36

Accepted Solution

Norie earned 2000 total points
ID: 36969763
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

ID: 36969765
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

ID: 36969778
That is exactly what I was looking at! Thanks!

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

578 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