CODEWITHSUNDEEP
excelvba
B.Cole
B.Cole

Reputation: 59

Extract and Copy text between characters in excel

I am looking for a way to extract text between 2 characters and copy them to a column at the end of the spreadsheet.

For example, my text looks like [abcdefg], all in Column A, I am looking to extract the text between the "[" and copy it to a new column at the end of the worksheet (as a new column)

Thanks

Upvotes: 0

Views: 4486

Answers (3)

Devin Vyain
Devin Vyain

Reputation: 144

If you want to write this into a vba module, looping through all entries in the A column and extracting the text inside the [] into an entry in column B, it might look something like:

Dim ws as Worksheet
Dim i as integer
Dim lastRow as Integer
Dim brack1pos as Integer
Dim brack2pos as Integer

Set ws = ThisWorkbook.Worksheets("My Sheet Name")

for i = 1 to lastRow  'I'll leave to you how to find the last row
     brack1pos = InStr(ws.Range("A" & i), "[")
     brack2pos = InStr(ws.Range("A" & i), "]")
     ws.Range("B" & i) = Trim(Mid(ws.Range("A" & i), brack1pos + 1, brack2pos - brack1pos - 1))
next

Upvotes: 0

JosephC
JosephC

Reputation: 929

This is for text in Cell 1, 1 but you can toss some variables in there as you loop through your rows. It will grab the values within square brackets & trim it, additional text can be in front or behind the brackets.

    Dim iPos1 As Integer
    Dim iPos2 As Integer
    iPos1 = InStr(Sheet1.Cells(1, 1), "[")
    iPos2 = InStr(Sheet1.Cells(1, 1), "]")

    Sheet1.Cells(1, 2) = Trim(Mid(Sheet1.Cells(1, 1), iPos1 + 1, iPos2 - iPos1 - 1))

Upvotes: 0

John Bustos
John Bustos

Reputation: 19544

I would resort to functions since they're just the easiest. To pull the data between 2 characters, you'd use a mixture of MID and FIND functions.

So, assuming your data was in cell A1, you could put this formula in the cell where you want the parsed value:

=MID(A1,FIND("[",A1)+1,FIND("]",A1)-FIND("[",A1)-1)

If you wanted to automate it, you could put it into a macro then copy / paste-special values to remove the function and keep the values.

Upvotes: 1

Related Questions