Split text to column based on semicolon as delimited with column header

user_15785313 0
Viewed 148

I am new to VBA Macro. I am trying to create a code that converts "Text to column" identifying semicolon as delimited. There is data in column has which has 1 values with ; as separator in between

Actual data

enter image description here

I want the macro to convert this into text to column the data after semicolon should move in next column with heading as KPI

Desired output should be like this in below image

Desired output

enter image description here

is it possible after text to column the new column name should be KPI as shown in desired output. Please guide I tried with below code nothings works out.

Sub TextToCol1()

Dim ws As Worksheet

Set ws = ActiveWorkbook.Worksheets("Exp")

ws.Range("A1").End(xlUp).TextToColumns , Semicolon:=True
  
End Sub
vba excel
1 Answers

I think you could try:

Sub TextToCol()

Dim ws As Worksheet, lRow As Long
Set ws = ActiveWorkbook.Worksheets("Exp")

With ws
    lRow = .Cells(.Rows.Count, "A").End(xlUp).Row
    .Range("A2:A" & lRow).TextToColumns Semicolon:=True
    '.Range("A2:A" & lRow).TextToColumns ConsecutiveDelimiter:=True, Semicolon:=True, Space:=True 'Option 1
    '.Range("A2:B" & lRow).Value = Application.Trim(.Range("A2:B" & lRow)) 'Option 2
    .Cells(1, 2) = "KPI"
End With

End Sub

Note that I added two optional lines. This is because I noticed trailing spaces in column A after I executed the code. To cater for this I opted to also split on spaces and treat these seperate delimiters as one! Or... you can Trim() the whole range and keep the simpler TextToColumns() method.

user_97581940
Related