Forum Discussion
Extraction formula help
I need a formula to extract the AC-UG from the text string below and then put them in two separate columns without using the text-to-columns feature - TIA! EM-AC-UG-Course-ENG-122-Week3-TOT
I opened your attachment, and this is what I need returned...But I do not see the formula and how you produced this. Also, could you explain a little more about the Power Query option?
Sub extract()
Dim i As Long
Dim j As Long
Dim first As Long
Dim second As Long
Dim third As Long
j = Range("B" & Rows.Count).End(xlUp).Row
For i = 2 To j
first = Application.WorksheetFunction.Find("-", Cells(i, 2))
second = Application.WorksheetFunction.Find("-", Cells(i, 2), first + 1)
third = Application.WorksheetFunction.Find("-", Cells(i, 2), second + 1)
Cells(i, 3).Value = Mid(Cells(i, 2), first + 1, second - first - 1)
Cells(i, 4).Value = Mid(Cells(i, 2), second + 1, third - second - 1)
Next i
End SubAnother alternative could be VBA code. In the attached file you can click the button in cell F2 to run the macro.