Forum Discussion

Copper Contributor

Jul 14, 2022

Solved

Normalizing a text with numbers

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 d...

excel

Macros and VBA

Rsartori76
Jul 14, 2022
GKrembsler
Hi, 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

Rsartori76

Brass Contributor

Jul 14, 2022

GKrembsler

Hi, 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

GKrembsler
Copper Contributor
Jul 14, 2022
Here it is - my beloved split function i know from Script programming in HCL Notes. I just did not come to the idea to search for it in VBA documentation.
Thanks @ Rsartori76 - this is a much more elegant way to solve the problem.
Best wishes...