Josh Ord-Hume


I am trying to create an Excel macro that will 'look' at the text contents of a given cell (say A1), copy any text that occurs within brackets, and then deposit this text into an adjacent cell (B1). For example, if A1 contained the following: "This is <just> a test", the word "just" would be extracted and deposited into B1. Ideally, I would like to be able to run such a macro on a specifed range of rows...
Could anyone suggest any possibilities

Many thanks...

Josh



Re: VBA macro for Excel for extracting and 'depositing' text between brackets...

MyLady


Hey Josh,

Here is one suggestion...

The specified range in this code starts in cell A1 of the worksheet and the first blank defines the end of the data in Column A. The text, within brackets < >, is copied to the opposite cells in Column B

Sub CopyAndDepositTextWithinBrackets()
Dim rngCell As Range
Dim strName As String
Dim OpenBracket As Integer
Dim CloseBracket As Integer
For Each rngCell In Range("A1", Range("A1").End(xlDown))
strName = rngCell.Value
OpenBracket = InStr(1, strName, "<")
CloseBracket = InStr(1, strName, ">")
rngCell.Offset(0, 1).Value = Mid(strName, _
OpenBracket + 1, CloseBracket - OpenBracket - 1)
Next rngCell
End Sub

Best Regards

Cathrine






Re: VBA macro for Excel for extracting and 'depositing' text between brackets...

bi-lya

Or like this

Code Snippet

Dim r As Range

For Each r In Range("A1", "A" & Range("A1").SpecialCells(xlLastCell).Row)
If r Like "*<*>*" Then r.Offset(, 1).Value = Split(Split(r, Chr(60))(1), Chr(62))(0)
Next






Re: VBA macro for Excel for extracting and 'depositing' text between brackets...

Josh Ord-Hume

Dear Catherine...
This actually works and even I (a complete programming ignoramus) have been able to implement it. I'm very grateful - thank you very much - you have saved me hours and hours of painstaking manual selecting, cutting and pasting.
I wonder if I could ask you one further favour If you have time, could you possibly tell me - only if you have a spare ten minutes of so - what each line of code does I'm trying to learn VBA, but am doing it in a rather haphazard, non-linear way. If you could explain the rationale behind each snippet, I'm sure I could learn something...
Thanks again.
All the best...

Josh





Re: VBA macro for Excel for extracting and 'depositing' text between brackets...

Josh Ord-Hume

Dear bi-lya,
Many thanks - an elegant, concise solution that does exactly what I needed it to do!

All the best...

Josh





Re: VBA macro for Excel for extracting and 'depositing' text between brackets...

MyLady

Hey,

Here you goˇ­

Sub CopyAndDepositTextWithinBrackets()
'Declaring variables like Integers and Strings, and object types like Ranges
Dim rngCell As Range
Dim strName As String
Dim OpenBracket As Integer
Dim CloseBracket As Integer
'Define a For..Next loop from cell A1 to the last entry in column A
For Each rngCell In Range("A1", Range("A1").End(xlDown))
'Store the entry in the rngCell
strName = rngCell.Value
'Find the position of the brackets
OpenBracket = InStr(1, strName, "<")
CloseBracket = InStr(1, strName, ">")
'Extract the value inside the brackets and write it to the next cell to the right
rngCell.Offset(0, 1).Value = Mid(strName, _
OpenBracket + 1, CloseBracket - OpenBracket - 1)
'Loop until all cells is done
Next rngCell
End Sub

Cath

ˇ­remember to mark the answer as answered if it was what you were looking fore