CODEWITHSUNDEEP
excelvba
Paolo Medina
Paolo Medina

Reputation: 301

Add zero on a single digit string after "/" in a string

I have a column with the following format:

RMB-2018-726/12    
RMB-2019-27/16    
RMB-2019-160/21
RMB-2019-259/6
RMB-2019-433/2

Now what I need is to check the last digit or after the slash, if it is single digit add zero otherwise retain the value (for sorting purposes). I have already written a code using texttocolumn, len then merge columns, however it seems my solutions is long but it's working as per my needs. For the sake of learning, I am wondering if there are other work around or more effective way to do this. 

'Insert zero on single digit claim number
Dim myLastRow As Long
myLastRow = Worksheets(ActiveSheet.Name).Range("A1").End(xlDown).Row

Columns("J:M").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove 'Insert 4 new columns
'Column 1 = Batch Number; Column 2 = Claim Number; Column 3 = add zero on single digit; Column 4 = merge Column 1 + 3

Range("I2:I" & myLastRow).TextToColumns _
Destination:=Range("J2"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=True, Other:=True, OtherChar:= _
        "/" 'TextToColumn the Batch Column = Column 1
Application.CutCopyMode = False
Range("L2:L" & myLastRow).FormulaR1C1 = "=IF(LEN(RC[-1])=1,""0"","""")&RC[-1]" 'add zero on single digit only = Column 3
Range("M2:M" & myLastRow).FormulaR1C1 = "=RC[-3]&""/""&RC[-1]" 'combine the batch = Column 4

Range("M2:M" & myLastRow).Copy
Range("I2:I" & myLastRow).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False 'Where the original batch column
Columns("J:M").Delete
Columns("I:I").EntireColumn.AutoFit

Upvotes: 0

Views: 211

Answers (3)

bobajob
bobajob

Reputation: 1192

As an FYI, this can be done without vba:

=LEFT(A1,FIND("/",A1))&IF(MID(A1,LEN(A1)-2,1)="/",RIGHT(A1,2),"0"&RIGHT(A1,1))

Or, adding a zero if needed before the slash as well (as requested in a comment on another answer):

=LEFT(A1,9)&IF(MID(A1,13,1)="/",MID(A1,10,4),"0"&MID(A1,10,3))&IF(MID(A1,LEN(A1)-2,1)="/",RIGHT(A1,2),"0"&RIGHT(A1,1))

Note than any more complications (the possibility of a single digit before the slash for example) and this will quickly become unwieldy, so for extensibility vba is definitely the better option.

Upvotes: 1

TinMan
TinMan

Reputation: 7759

Here is a simple pattern that I use all the time to update Range values super efficiently.

  • Define target range
  • Read the target range values into an array
  • Modify the values
  • Assign the array back to the target range
Sub FormatIDs()
    Dim data
    Dim Target As Range
    Dim BackSlashIndex As Long, r As Long

    With ActiveSheet
        Set Target = .Range("A1", .Cells(Rows.Count, 1).End(xlUp))
    End With

    data = Target.Value

    For r = 1 To UBound(data)
        BackSlashIndex = InStrRev(data(r, 1), "/")
        If Len(data(r, 1)) - BackSlashIndex = 1 Then
            data(r, 1) = Left(data(r, 1), Len(data(r, 1)) - 1) & "0" & Right(data(r, 1), 1)
        End If
    Next

    Target.Value = data
End Sub

Upvotes: 1

Dmitrij Holkin
Dmitrij Holkin

Reputation: 2055

Maybe this can help

Sub test()
Dim wb As Workbook, ws As Worksheet
    Set wb = ThisWorkbook
    Set ws = wb.Worksheets("Sheet1")
    colLatter = "A"
        LastRow = ws.Cells(ws.Rows.Count, colLatter).End(xlUp).Row
        valArr = ws.Range(colLatter & "1:" & colLatter & LastRow).Value
        For i = LBound(valArr) To UBound(valArr)
            If valArr(i, 1) <> "" Then
                sStr = Split(valArr(i, 1), "/")
                valArr(i, 1) = sStr(0) & "/" & Format(sStr(1), "00")
            End If
        Next i
        ws.Range(colLatter & "1").Resize(UBound(valArr), 1) = valArr
End Sub

Upvotes: 1

Related Questions