Keep leading zero in number (Column also contains text)
 

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

Keep leading zero in number (Column also contains text)
 

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" skrev i meddelelsen
...
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

Keep leading zero in number (Column also contains text)
 

On Apr 16, 2:26*am, "Per Jessen" wrote:
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" skrev i ...



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- Hide quoted text -

- 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

Keep leading zero in number (Column also contains text)
 

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" skrev i meddelelsen
...
On Apr 16, 2:26 am, "Per Jessen" wrote:
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" skrev i
...



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- Hide quoted text -

- 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