CODEWITHSUNDEEP
vbaexcel
Mohamad Faisal
Mohamad Faisal

Reputation: 81

Excel-VBA: Combine String-Number from Multi Column Table

I have a table of data which consists of;

Column A: Name of person and Number e.g: Alexander25 (text and number)
Column B: Code of person e.g: AD-4 (text and number)
Column C: RingName of person e.g: Alex Cooper (only text)

In the table, Column A and B is filled out every row while Column C is optional (sometimes do have while sometimes do not have). I want to create new column using VBA to combine all these column to make something like this;
Column D: Alex025 Cooper: AD-4

where this only applicable if column C is present. The name at column C might have same text with Column A, though I want only to take what is different. Eg if the name is Alexander and the ring name is Alex Cooper, I just want take the Cooper only. The number is varies and it can be up to 3-digits only. If the number is;
1 digits- 00x
2 digits- 0xx
3 digits- xxx

Appreciate with any opinions if any is possible. Thanks stackoverflow.com

Upvotes: 0

Views: 125

Answers (1)

DisplayName
DisplayName

Reputation: 13386

you could use this:

Option Explicit

Sub main()
    Dim cell As Range

    For Each cell In Range("C1", Cells(Rows.Count, 3).End(xlUp))
        cell.Offset(, 1).Value = Split(cell.Value, " ")(0) & Format(GetLastDigits(cell.Offset(, -2)), "000") & " " & Split(cell.Value, " ")(1) & ": " & cell.Offset(, -1).Value
    Next
End Sub

Function GetLastDigits(strng As String) As String
    Dim i As Long

    Do While IsNumeric(Right(strng, i + 1))
        i = i + 1
    Loop
    GetLastDigits = Right(strng, i)
End Function

Upvotes: 1

Related Questions