Forum Discussion

GKrembsler's avatar
GKrembsler
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's avatar
    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

     

     

OliverScheurich's avatar
OliverScheurich
Gold Contributor
Jul 14, 2022

GKrembsler 

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

 

GKrembsler's avatar
GKrembsler
Copper Contributor
Jul 14, 2022
Tank you - this is the more manual way with a formula but not exactly what i am looking for.
Guess i did not make clear my intention 😉
I want to click on a button / start a macro, that is doing the work for me.
No problem to loop through every cell, get the value an change the last number after the last dot.
What i am looking for is a kind of "Function" doing this on a "search an replace" way.
Like having an RegEx expression.
But i guess it is really the simple way to loop through all cells.
  • GKrembsler's avatar
    GKrembsler
    Copper Contributor
    Jul 14, 2022
    This is my current Macro, in case it helps someone else 😉

    Sub Normalize_Controls()
    '
    ' Normalize_Controls Makro
    '

    '
    For i = 1 To 9
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Replace What:="." & i & ".", Replacement:=".0" & i & ".", LookAt:=xlPart,SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False, FormulaVersion:=xlReplaceFormula2
    Next

    For Each cel In Selection.Cells
    TxTString = ""
    FirstString = ""
    LastString = ""
    TxTString = cel.Text

    FirstString = Left(TxTString, InStrRev(TxTString, "."))
    LastString = Mid(TxTString, InStrRev(TxTString, ".") + 1, 999)

    If Len(LastString) = 1 Then cel.Value = FirstString & "0" & LastString

    Next cel

    End Sub
    • HansVogelaar's avatar
      HansVogelaar
      MVP
      Jul 14, 2022

      GKrembsler 

      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

       

Resources