View Full Version : place 0 before numbers in a string
Tony Singh
06-06-2014, 07:18 AM
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
06-06-2014, 08:57 AM
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
06-06-2014, 09:24 AM
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
06-06-2014, 12:03 PM
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
06-06-2014, 12:13 PM
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
06-06-2014, 12:26 PM
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
06-06-2014, 12:40 PM
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
06-06-2014, 01:52 PM
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
06-06-2014, 07:46 PM
You can avoid testing altogether and use
c.Value = "'" & Format(c.Value,"00000000")
Paul_Hossler
06-07-2014, 08:28 AM
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
06-07-2014, 06:26 PM
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.
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
Sub M_snb()
Selection.Name = "snb"
[snb] = [index(text(snb,"'00000000"),)]
End Sub
Tony Singh
06-09-2014, 04:11 AM
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
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.