How to pull string from cell in Excel after repeating characters?

0
votes

I have a cell with the following value:

5661:GRE:08:NAME

I'd like to find an excel formula that can help me extrapolate from this example the following values into separate columns/cells: 5661 GRE 08 NAME

I have used LEN, MID, FIND in the past but that was only to find a single "delimiter" like : in the above example. Not sure how to figure this out to find values when the delimiter repeats several times in a cell. Would rather try to solve this in formulas than have to use VBA.

2
excelexcel-formula

2 Answers

5
votes

Use Text to Columns from the Data Tab:

Step one: Choose Delimited hit Next

enter image description here

Step Two: Click other and put : in the box.

enter image description here

From here you can go on to step three, but it is optional. Step three allows the user to dictate limit format types.

If you do not care that the columns stay as General then click Finish.

enter image description here

If you want a formula then:

=TRIM(MID(SUBSTITUTE($A1,":",REPT(" ",999)),999*(COLUMN(A:A)-1)+1,999))

As you drag/copy to the right it will get the next word.

enter image description here

1
votes

Suppose your data in cell A1, Run the second Macro

Sub text() Call Extract_Any_Think(Range("a1"), "\w+") End Sub

Option Explicit

Sub Extract_Any_Think(st As Range, Patt$)
  Dim Obj As Object, i%
  Set Obj = CreateObject("Vbscript.RegExp")
  With Obj
   .Pattern = Patt
   .Global = True
  End With
   If Obj.test(st) Then
     For i = 0 To Obj.Execute(st).Count - 1
     st.Offset(, i + 2) = Obj.Execute(st)(i).Value
     Next
   End If
End Sub

'+++++++++++++++++++++++++++++++

Sub text()
Call Extract_Any_Think(Range("a1"), "\w+")
End Sub