leading zero / vba to change number format?

[mjd]

Hi,

I have an odd issue. I've got an ever changing list of account numbers, the bulk of which have leading zeros. In excel, I obviously have to have this column formatted as text in order to maintain those leading zeros.

Now, the problem is that I'm querying off this list with a vlookup, and am getting errors for the data that does not have leading zeros.

is there a way to script it so that the non-leading zero data points can be formatted as general? when i do it manually it fixes everything, but this is a very tedious process and defeats the point of what I'm accomplishing with this project.

Thanks,
Mike

 

[VoG]

Maybe like this

=VLOOKUP(TEXT(A1,"0000"),B1:D200,2,FALSE)

 

[mjd]

Maybe like this

=VLOOKUP(TEXT(A1,"0000"),B1:D200,2,FALSE)

sorry for the delayed response, that works perfectly. thanks!

 

[mjd]

Revisiting this....

The above solution doesn't seem to be working for the project I'm working on. With that said, I've found the following code:

Sub macro()
    Range("a:a").Select
    With Selection
        Selection.NumberFormat = "General"
        .Value = .Value
    End With
End Sub

Is there a way to modify this code to search column A and keep entries with leading zeros as text (numbers stored as text) and convert all other items to general?

Thanks,
Mike

 

[vds1]

Try This,

Sub macro()
    Range("A:A").NumberFormat = "0000"
End Sub

 

[mjd]

Try This,

Sub macro()
    Range("A:A").NumberFormat = "0000"
End Sub

VDS, unfortunately that does not clear the "text stored as number" error for the pure numeric entries that do not begin with zero

The below is a redacted sample of my data. Column B is how i need the formatting to be when this is all said and done.

Sample Excel Format I need
7HQ10 general
81018 general
7GS13 general
09521 text stored as number
13166 general


Thanks,
mike

 

[vds1]

Try this,

Select Column A -> Format Cells as General
Select Column A -> Goto Data -> Text to column -> Click Finish

If this doesnt help show me sample of Input Data and output Data for better Clarity on what you are trying to accomplish.

 

[mjd]

VDS, Thanks, but that would only remove the leading zeros which we need.

Essentially I need a script that is smart enough convert everything in column A that does NOT begin with zero to the general format.

 

[mjd]

I think the following is close; though it is failing on where I need the cell to be reformatted as general.

Thoughts?

Sub Macro1()
    LastRow = Range("a" & Rows.Count).End(xlUp).Row
        For i = LastRow To 1 Step -1
        If Cells(i, "a").Value <> "0*" Then
        [COLOR="Red"]Cells(i, "a").Format.General[/COLOR]
        End If
        Next
End Sub

Thanks,
mike

 

[beastman59]

Try This,

Sub macro()
    Range("A:A").NumberFormat = "0000"
End Sub

Thanks! It just helped me...I found it searching with Google