Jul 14 2022 03:04 AM
Hello,
i am looking for a fast ans simple (even VBA) solution to normalize text in a column.
Let's say i have column B with values in the pattern
A.9.1.1
What i want is every number to have two digits with a leading zero.
It is easy to replace .1. with .01. with a vba macro in a loop.
But the number at the end is difficult.
I can not replace .1 with .01 as for example A.10.01.1 would become to A.010.1.
You can see my problem?
Of course i could loop through each cell value, grab the text after teh last dot an check if it is one or two digits.
If it is one digit i can add the trailing zero.
Is this the only way or ist there some secret function that will do this for me faster?
Jul 14 2022 03:34 AM
=LEFT(B8,SEARCH(".",B8)-1)&"."&TEXT(MID(B8,SEARCH(".",B8)+1,SEARCH(".",B8,SEARCH(".",B8)+1)-1-SEARCH(".",B8)),"00")&"."&TEXT(MID(B8,SEARCH(".",B8,SEARCH(".",B8)+1)+1,SEARCH(".",B8,SEARCH(".",B8,SEARCH(".",B8)+1)+1)-1-SEARCH(".",B8,SEARCH(".",B8)+1)),"00")&"."&TEXT(RIGHT(B8,LEN(B8)-SEARCH(".",B8,SEARCH(".",B8,SEARCH(".",B8)+1)+1)),"00")
Maybe with this formula. I've entered the formula in cell B12 and copied to the right.
Jul 14 2022 03:45 AM
Jul 14 2022 03:54 AM
Jul 14 2022 04:42 AM
Sub AddLeadingZeros()
Dim c As Range
Dim s As String
Dim i As Long
Application.ScreenUpdating = False
For Each c In Selection
s = Replace(c.Value, ".", "..") & "."
For i = 0 To 9
s = Replace(s, "." & i & ".", ".0" & i & ".")
Next i
s = Replace(s, "..", ".")
c.Value = Left(s, Len(s) - 1)
Next c
Application.ScreenUpdating = True
End Sub
Jul 14 2022 04:49 AM
SolutionHi, see if this works. It presupposes the first char is always a letter and add zeroes for any number with length < 2.
Sub AddLeadingZero()
'
' AddLeadingZero Macro
'
Dim numCodes() As Variant
Dim codeText() As String
Dim codeRange As Range
Set codeRange = Selection
numCodes = codeRange
For i = 1 To UBound(numCodes)
codeText = Split(numCodes(i, 1), ".")
For j = 1 To UBound(codeText)
If Len(codeText(j)) < 2 Then codeText(j) = "0" & codeText(j)
Next j
numCodes(i, 1) = Join(codeText, ".")
Next i
codeRange = numCodes
End Sub
Jul 14 2022 05:22 AM
Jul 14 2022 04:49 AM
SolutionHi, see if this works. It presupposes the first char is always a letter and add zeroes for any number with length < 2.
Sub AddLeadingZero()
'
' AddLeadingZero Macro
'
Dim numCodes() As Variant
Dim codeText() As String
Dim codeRange As Range
Set codeRange = Selection
numCodes = codeRange
For i = 1 To UBound(numCodes)
codeText = Split(numCodes(i, 1), ".")
For j = 1 To UBound(codeText)
If Len(codeText(j)) < 2 Then codeText(j) = "0" & codeText(j)
Next j
numCodes(i, 1) = Join(codeText, ".")
Next i
codeRange = numCodes
End Sub