Keep leading zero in number (Column also contains text)

[steve1040]

I have a column with different product ID

Some of the prodID are all text, some are mixed text & num and some
are numbers only.

I formatted the column to text and fixed everything manually.

The data comes from an external source - When I refresh the data some
of the data from the provider have leading zero and some don't and
it's breaking my lookups.

Is it possible to have a macro check each cell in the column (B)
Even though column is formatted as text I want to:

If value is all numeric and does not start with zero add zero
otherwise do nothing.

So if my data looks like this
014-07899-00E
014-W7599-TRE
015-40649-A
01553
01559
01561
100384
100443
100519
100537

I'd like it to look like this

014-07899-00E
014-W7599-TRE
015-40649-A
01553
01559
01561
0100384
0100443
0100519
0100537

 

[Per Jessen]

This should do it:

Sub AddZero()
TargetCol = "B"
FirstRow = 2 ' Headings in row 1
LastRow = Range(TargetCol & Rows.Count).End(xlUp).Row

For r = FirstRow To LastRow
If Left(Range(TargetCol & r).Value, 1) <> 0 Then
Range(TargetCol & r).Value = 0 & Range(TargetCol & r).Value
End If
Next
End Sub

Regards,
Per

 

[steve1040]

This should do it:

Sub AddZero()
TargetCol = "B"
FirstRow = 2 ' Headings in row 1
LastRow = Range(TargetCol & Rows.Count).End(xlUp).Row

For r = FirstRow To LastRow
    If Left(Range(TargetCol & r).Value, 1) <> 0 Then
        Range(TargetCol & r).Value = 0 & Range(TargetCol & r).Value
    End If
Next
End Sub

Regards,
Per

"steve1040" <[email protected]> skrev i meddelelsen











- Show quoted text -

Thanks
but this looks like it would put a leading 0 in front of every value
in the column.

I'm thinking I'll have to check every characters in each cell and move
on to next cell when condition is false.
It's 3 am - I'll tackle tomorrow (later on this morning)

Beside - a Windows update wants to restart my computer

 

[Per Jessen]

Hi Steve

You are right, I missed the numeric condition in my first code.

Sub AddZero()
TargetCol = "B"
FirstRow = 2 ' Headings in row 1
LastRow = Range(TargetCol & Rows.Count).End(xlUp).Row

For r = FirstRow To LastRow
If IsNumeric(Range(TargetCol & r).Value) Then
If Left(Range(TargetCol & r).Value, 1) <> 0 Then
Range(TargetCol & r).Value = 0 & Range(TargetCol & r).Value
End If
End If
Next
End Sub

Regards,
Per

"steve1040" <[email protected]> skrev i meddelelsen

This should do it:

Sub AddZero()
TargetCol = "B"
FirstRow = 2 ' Headings in row 1
LastRow = Range(TargetCol & Rows.Count).End(xlUp).Row

For r = FirstRow To LastRow
If Left(Range(TargetCol & r).Value, 1) <> 0 Then
Range(TargetCol & r).Value = 0 & Range(TargetCol & r).Value
End If
Next
End Sub

Regards,
Per

"steve1040" <[email protected]> skrev i
meddelelsen











- Show quoted text -

Thanks
but this looks like it would put a leading 0 in front of every value
in the column.

I'm thinking I'll have to check every characters in each cell and move
on to next cell when condition is false.
It's 3 am - I'll tackle tomorrow (later on this morning)

Beside - a Windows update wants to restart my computer