place 0 before numbers in a string

[Tony Singh]

Hi

I have the following code that inserts an X at the end of a text string if the there are less than 8 letters.

Sub Macro2()
Dim C As Range
For Each C In Selection
If C.Value <> "" and len(C.Value) < 8 Then C.Value = C.Value & String(8 - len(C.Value),"X")
Next
End Sub

I want to tweak this code to work on a column containing cells with numbers that must consist of upto 8 in length.
If a cell contains less than 8 numbers, then a 0 (Zero) should be inserted at the start to make up a total 8 numbers in the cell.

I would appreciate help from anybody.

Thanks
Tony

[Bob Phillips]

Sub Macro2()
Dim C As Range
    For Each C In Selection
        If C.Value <> "" And Len(C.Value) < 8 Then C.Value = Left("0000000" & C.Value, 8)
    Next
End Sub

[Tony Singh]

Xld

Thanks for your reply, but the code actually trimmed off the numbers instead of completing the set to 8 digits, ie if a cell contains '123456' the code should complete it to '00123456'.

The reason behind this is that the Bacs system I use only recognises 8 digits. Hope you can take another look.

Thanks
Tony

[p45cal]

Xld

Thanks for your reply, but the code actually trimmed off the numbers instead of completing the set to 8 digits, ie if a cell contains '123456' the code should complete it to '00123456'.

The reason behind this is that the Bacs system I use only recognises 8 digits. Hope you can take another look.

I think this is because the cells are not formatted as TEXT first, rather as General or a number, accounting etc.
Another solution may work; dispense with a macro and use a custom format for the cells of 00000000

[Zack Barresse]

You could also preceed your value with a single apostrophe ( ' ) to ensure the value in the cell is read as text. Although I'd recommend doing what p45cal says and format the cell as Text before you enter the value. The example is like this with VBA:

[vba]YourRange.NumberFormat = "@"

' or, with xld's code, inside the loop...

C.NumberFormat = "@"[/vba]

[Paul_Hossler]

Not tested, but .Text might work better than .Value for you

Sub Macro2A() 
    Dim  C As Range 
   For Each C In Selection.Cells
        If Len(C.Text) > 0  And Len(C.Text) < 8 Then C.Value = Left("0000000" & C.Text, 8) 
    Next
End Sub

[Zack Barresse]

The .Text property returns a read-only string, but the cell format will still take precedence, so that won't work. It would either need to be:

[vba]Sub Macro2A()
Dim C As Range
For Each C In Selection.Cells
If Len(C.Value) > 0 And Len(C.Value) < 8 Then C.Value = "'" & Left("0000000" & C.Value, 8)
Next
End Sub[/vba]
Or
[vba]Sub Macro2A()
Dim C As Range
For Each C In Selection.Cells
C.NumberFormat = "@"
If Len(C.Value) > 0 And Len(C.Value) < 8 Then C.Value = Left("0000000" & C.Value, 8)
Next
End Sub[/vba]

The first one doesn't change the cell format, but forces a textual cell value, while the second one changes the format prior to entry. Since there are odd issues when reading if a cell is preceeded with an apostrophe, it's generally considered more advantageous (for maintenance) to format the cell as text.

[Paul_Hossler]

I want to tweak this code to work on a column containing cells with numbers that must consist of up to 8 in length.
If a cell contains less than 8 numbers, then a 0 (Zero) should be inserted at the start to make up a total 8 numbers in the cell.

Zack -- you are correct about .Text

I read the question to mean that:

ABCD becomes ABCDXXXX

and

1234 becomes 00001234

So wouldn't 1234 use Right() and ABCD use Left()?

Option Explicit
Sub test3()
    Dim c As Range
    If Not TypeOf Selection Is Range Then Exit Sub
    
    Selection.NumberFormat = "@"
    
    For Each c In Selection.Cells
        If Len(c.Value) > 0 And Len(c.Value) < 8 Then
            If IsNumeric(c.Value) Then
                c.Value = Right("00000000" & c.Value, 8)
            Else
                c.Value = Left(c.Value & "XXXXXXXX", 8)
            End If
        End If
    Next
End Sub

[mikerickson]

You can avoid testing altogether and use

c.Value = "'" & Format(c.Value,"00000000")

[Paul_Hossler]

Hi Mike --

I might have assumed wrong that the OP wanted a single sub to do both

I have the following code that inserts an X at the end of a text string if the there are less than 8 letters.

If a cell contains less than 8 numbers, then a 0 (Zero) should be inserted at the start to make up a total 8 numbers in the cell.

So ABCD becomes ABCDXXXX and 1234 becomes 00001234

[mikerickson]

Ah yes, the differences between TEXT and Format.
=TEXT(A1,"00000000;00000000;00000000;@""X""") works, but that string can't be used with VBA's Format.

Perhaps

    Selection.NumberFormat = "00000000;00000000;00000000;@""X"""
    For Each C In Selection
         C.Value = C.Text
    Next oneCell

If its only for appearance, the loop can be omitted all together.

[GTO]

One more? Close to Paul's I believe; I just left the numbers as numbers.

Option Explicit
  
Sub aTry()
Dim rng As Range
Dim arr
Dim n As Long
  
  Set rng = Selection
  If (Not rng.Columns.Count = 1) Or (Not rng.Rows.Count > 1) Then
    MsgBox "Uhmmmm...."
    Exit Sub
  End If
  
  arr = rng.Value
  rng.Clear
  
  For n = 1 To UBound(arr)
    If Not arr(n, 1) = vbNullString Then
      If IsNumeric(arr(n, 1)) Then
        rng.Cells(n).NumberFormat = CStr("00000000")
      Else
        arr(n, 1) = arr(n, 1) & Right$("XXXXXXXX", Application.Max(0, 8 - Len(arr(n, 1))))
      End If
    End If
  Next
  
  rng.Value = arr
  
End Sub

[snb]

Sub M_snb()
    Selection.Name = "snb"
    [snb] = [index(text(snb,"'00000000"),)]
End Sub

[Tony Singh]

Thankyou all, format to text worked. I will go through all the replies to see which vba code works.

Thanks once again to all.
Tony