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:

YourRange.NumberFormat = "@"

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

C.NumberFormat = "@"

[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:

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
Or
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

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 oneCellIf 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