Solved: add leading zero(s)

[spartacus132]

Hi All,
I have a bunch of columns in a sheet, each cell in that column has a numeric value.

I need to loop through each cell till the end of each column and add leading zero(s) till the length of the cell value is equal to 10.

Example 1:

orginal value:
38068
after manipulation:
0000038068

orginal value:
45-068
after manipulation:
000045-068

any help will be appreciated, thanks!

[mvidas]

Hi Spartacus,

Use the formula
=RIGHT("0000000000"&A1,10)

Fill down/right as needed, then copy/paste special/values over the original one. Very easy, very quick, and you don't have to worry about different formats within the cells (like your 45-068).

Matt

[Oorang]

Non-VBA Way:
Assume data in column A & you want all data to be 10 chars in length.
Insert a column to the right of A. Type in =REPT("0",10-LEN(A1))&A1
Copy down.
Copy/Pastespecial values overtop of A.
Delete the temp column you inserted,

I tend to prefer this method because you don't have to count 0's (and I'm lazy). Just change the 10 to the desired number of characters.

VBA Way:Option Explicit
Sub PadCells()
On Error GoTo Error_Handler
Dim Cll As Excel.Range
Dim Data As Excel.Range
Dim TargetLength As Long
Dim CellLen As Long
Dim CellValue As String
Dim Truncate As VBA.VbMsgBoxResult
Const ForceText As String = "'"
Const Zero As String = "0"
Const LengthError As Long = 5
Set Data = Excel.Selection
TargetLength = Excel.Application.InputBox( _
"Enter a numeric value indicated the desired length of data in characters:", _
"Enter Target Cell Length", VBA.Len(Data.Cells(1, 1).Value), Type:=1)
If TargetLength = 0 Then 'Detect Cancel
MsgBox "Operation Aborted", vbInformation + vbMsgBoxSetForeground
Exit Sub
End If
For Each Cll In Data.Cells
If Not Cll.HasFormula Then
CellValue = Cll.Value
CellLen = VBA.Len(CellValue)
Cll.Value = ForceText & _
VBA.String$(TargetLength - CellLen, Zero) & CellValue
End If
Next Cll
Exit Sub
Error_Handler:
If VBA.Err.Number = LengthError Then
Truncate = VBA.MsgBox("Encountered cell at " & Cll.Address & _
" with length of " & CellLen & _
", do you wish to truncate it's value to " & TargetLength & _
" characters? (If you select the new value will be """ & _
VBA.Right$(CellValue, TargetLength) & """.)", _
vbQuestion + vbYesNoCancel + vbMsgBoxSetForeground, "Invalid Length")
If Truncate = vbCancel Then
MsgBox "Operation Aborted", vbInformation + vbMsgBoxSetForeground
Exit Sub
ElseIf Truncate = vbYes Then
Cll.Value = ForceText & VBA.Right$(CellValue, TargetLength)
End If
Resume Next
End If
VBA.MsgBox VBA.Err.Number, vbCritical + vbMsgBoxSetForeground + _
vbMsgBoxHelpButton, "Error: " & VBA.Err.Number, VBA.Err.HelpFile, _
VBA.Err.HelpContext
End Sub

[spartacus132]

Matt & Oorang, thanks a lot for your help.

Much appreciated!

[spartacus132]

Oorang, you thought further than i did. I did have cells whose length was greater than 10, and i needed to ignore those cells. You code provided me with that option.

:)

[Oorang]

lol I used to work in a Bank, what I gave is a VERY stripped down version of a feature in an add-in I wrote. I had plenty of time to discover all the things that go wrong.
I didn't want to complicate it, but consider adding an auto-trim option. You'll find that leading/trailing spaces will mess with you too:D

[Pam in TX]

This issue is very close to one I am currently trying to solve.... I have a macro that does this for the individual columns, but would love to do it for the concantinated columns as shown below....

For instance, I have 10 columns that currently contain 1-5-N and I need them to read as 01-05-N...... Sometimes the column will be blank (leave it alone, sometimes it will already be double digits (10-10-Y) leave alone)) In a nutshell, if it is a single digit, before the 2 hyphens, I need it to read as double digit, if double digit (10-10-Y), I need it left alone....

This would be a huge timesaver as this is done MANY times a day.......

Thank you in advance for the assistance......

[Bob Phillips]

=IF(K1="","",TEXT(MID(K1,1,FIND("-",K1)-1),"00-")&TEXT(MID(K1,FIND("-",K1)+1,FIND("-",K1,FIND("-",K1)+1)-FIND("-",K1)-1),"00-"))&RIGHT(K1,1)

[Oorang]

Hi Pam, this should do it.

Option Explicit
Sub PadCells()
On Error GoTo Error_Handler
Dim Cll As Excel.Range
Dim Data As Excel.Range
Dim TargetLength As Long
Dim SegmentLen As Long
Dim Truncate As VBA.VbMsgBoxResult
Dim Delimiter As String
Dim TmpArray As Variant
Dim SegmentIndex As Long
Const ForceText As String = "'"
Const Zero As String = "0"
Const LengthError As Long = 5
Set Data = Excel.Selection
TargetLength = Excel.Application.InputBox( _
"Enter a numeric value indicated the desired length of data in characters:", _
"Enter Target Cell Length", VBA.Len(Data.Cells(1, 1).Value), Type:=1)
If TargetLength = 0 Then 'Detect Cancel
MsgBox "Operation Aborted", vbInformation + vbMsgBoxSetForeground
Exit Sub
End If
Delimiter = VBA.InputBox("Enter the delimter to pad to:", "Enter Delimiter", "-")
If Delimiter = vbNullString Then 'Detect Cancel
MsgBox "Operation Aborted", vbInformation + vbMsgBoxSetForeground
Exit Sub
End If
For Each Cll In Data.Cells
If Not Cll.HasFormula Then
TmpArray = VBA.Split(Cll.Value, Delimiter)
For SegmentIndex = Zero To UBound(TmpArray)
SegmentLen = VBA.Len(TmpArray(SegmentIndex))
TmpArray(SegmentIndex) = VBA.String$(TargetLength - SegmentLen, Zero) & TmpArray(SegmentIndex)
Next
Cll.Value = ForceText & VBA.Join(TmpArray, Delimiter)
End If
Next Cll
Exit Sub
Error_Handler:
If VBA.Err.Number = LengthError Then
Truncate = VBA.MsgBox("Encountered cell at " & Cll.Address & _
" with length of " & SegmentLen & _
", do you wish to truncate it's value to " & TargetLength & _
" characters? (If you select the new value will be """ & _
VBA.Right$(TmpArray(SegmentIndex), TargetLength) & """.)", _
vbQuestion + vbYesNoCancel + vbMsgBoxSetForeground, "Invalid Length")
If Truncate = vbCancel Then
MsgBox "Operation Aborted", vbInformation + vbMsgBoxSetForeground
Exit Sub
ElseIf Truncate = vbYes Then
TmpArray(SegmentIndex) = VBA.Right$(TmpArray(SegmentIndex), TargetLength)
End If
Resume Next
End If
VBA.MsgBox VBA.Err.Number, vbCritical + vbMsgBoxSetForeground + _
vbMsgBoxHelpButton, "Error: " & VBA.Err.Number, VBA.Err.HelpFile, _
VBA.Err.HelpContext
End Sub

[Pam in TX]

Hi Pam, this should do it.

Option Explicit
Sub PadCells()
On Error GoTo Error_Handler
Dim Cll As Excel.Range
Dim Data As Excel.Range
Dim TargetLength As Long
Dim SegmentLen As Long
Dim Truncate As VBA.VbMsgBoxResult
Dim Delimiter As String
Dim TmpArray As Variant
Dim SegmentIndex As Long
Const ForceText As String = "'"
Const Zero As String = "0"
Const LengthError As Long = 5
Set Data = Excel.Selection
TargetLength = Excel.Application.InputBox( _
"Enter a numeric value indicated the desired length of data in characters:", _
"Enter Target Cell Length", VBA.Len(Data.Cells(1, 1).Value), Type:=1)
If TargetLength = 0 Then 'Detect Cancel
MsgBox "Operation Aborted", vbInformation + vbMsgBoxSetForeground
Exit Sub
End If
Delimiter = VBA.InputBox("Enter the delimter to pad to:", "Enter Delimiter", "-")
If Delimiter = vbNullString Then 'Detect Cancel
MsgBox "Operation Aborted", vbInformation + vbMsgBoxSetForeground
Exit Sub
End If
For Each Cll In Data.Cells
If Not Cll.HasFormula Then
TmpArray = VBA.Split(Cll.Value, Delimiter)
For SegmentIndex = Zero To UBound(TmpArray)
SegmentLen = VBA.Len(TmpArray(SegmentIndex))
TmpArray(SegmentIndex) = VBA.String$(TargetLength - SegmentLen, Zero) & TmpArray(SegmentIndex)
Next
Cll.Value = ForceText & VBA.Join(TmpArray, Delimiter)
End If
Next Cll
Exit Sub
Error_Handler:
If VBA.Err.Number = LengthError Then
Truncate = VBA.MsgBox("Encountered cell at " & Cll.Address & _
" with length of " & SegmentLen & _
", do you wish to truncate it's value to " & TargetLength & _
" characters? (If you select the new value will be """ & _
VBA.Right$(TmpArray(SegmentIndex), TargetLength) & """.)", _
vbQuestion + vbYesNoCancel + vbMsgBoxSetForeground, "Invalid Length")
If Truncate = vbCancel Then
MsgBox "Operation Aborted", vbInformation + vbMsgBoxSetForeground
Exit Sub
ElseIf Truncate = vbYes Then
TmpArray(SegmentIndex) = VBA.Right$(TmpArray(SegmentIndex), TargetLength)
End If
Resume Next
End If
VBA.MsgBox VBA.Err.Number, vbCritical + vbMsgBoxSetForeground + _
vbMsgBoxHelpButton, "Error: " & VBA.Err.Number, VBA.Err.HelpFile, _
VBA.Err.HelpContext
End Sub


Aaron,

Works great with one exception..... I don't want a 0 before the Y or N after the last hyphen.....

I am so new to this and really appreciate your help.....

Thanks,
PAM

[Pam in TX]

Another thing I thought of this information will always be in the same columns. So can it be written for columns specific? and how difficult would that be?

[Oorang]

Change this:Set Data = Excel.SelectionTo:Set Data = Excel.Intersect(Excel.Selection.EntireColumn, Excel.Selection.Parent.UsedRange)

[Pam in TX]

Change this:Set Data = Excel.SelectionTo:Set Data = Excel.Intersect(Excel.Selection.EntireColumn, Excel.Selection.Parent.UsedRange)

I must have done something wrong..... I don't see any change in how it performed.....

I really appreciate your patience and taking the time to answer my questions and help solve this issue..... It is very kind of you to do this....

[Oorang]

The original works on all selected cells. The new one works on the entire column of and any selected cell. (So if you select A4:B5 it does the Entire Columns of A and B). If this was not your intent perhaps it would help if you clarified ?

[Pam in TX]

The original works on all selected cells. The new one works on the entire column of and any selected cell. (So if you select A4:B5 it does the Entire Columns of A and B). If this was not your intent perhaps it would help if you clarified ?

It does put a leading 0 for the numbers, but does it on the Y and N also... I need it to end up as 07-10-Y......

I am sorry if I didn't clarify that piece....

Thanks again....

[Pam in TX]

Aaron,

Never mind.....

I just did a simple find and replace macro for the Y and N, then added it to the code you provided..........

Thanks again for your patience and assistance.....

[Oorang]

Hi Pam,
That works too:) You could do a IsNumeric test on each segment and not pad it unless it evaluates as a number. You would changeFor SegmentIndex = Zero To UBound(TmpArray)
SegmentLen = VBA.Len(TmpArray(SegmentIndex))
TmpArray(SegmentIndex) = VBA.String$(TargetLength - SegmentLen, Zero) & TmpArray(SegmentIndex)
NextTo:For SegmentIndex = Zero To UBound(TmpArray)
If VBA.IsNumeric(TmpArray(SegmentIndex)) Then
SegmentLen = VBA.Len(TmpArray(SegmentIndex))
TmpArray(SegmentIndex) = VBA.String$(TargetLength - SegmentLen, Zero) & TmpArray(SegmentIndex)
End If
Next