Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
leading zeros when uploading a number from text file | Excel Discussion (Misc queries) | |||
zero supress leading zeros when chg format from text to number | Excel Worksheet Functions | |||
creating a text record from a number preserving the leading zero | Excel Programming | |||
Number to text with leading zero | Excel Programming | |||
Force a Number to be Formatted as Text (keep leading 0's) | Excel Programming |