find wildcard text between {} cut, and paste in another cell
It works perfectly! THANK YOU!
"Rick Rothstein" a scris:
Sorry, I just noticed you wanted all the comments in the same cell. Try this
macro instead...
Sub ParseComments()
Dim X As Long, Z As Long
Dim DataLastRow As Long
Dim Text As String
Dim Parts() As String
Const DataStartRow As Long = 2
Const DataColumn As String = "A"
Const WorksheetName As String = "Sheet1"
With Worksheets(WorksheetName)
DataLastRow = .Cells(.Rows.Count, DataColumn).End(xlUp).Row
For X = DataStartRow To DataLastRow
Text = ""
With .Cells(X, DataColumn)
Parts = Split(Replace(.Value, "}", "{"), "{")
For Z = 1 To UBound(Parts) Step 2
Text = Text & "{" & Parts(Z) & "} "
Parts(Z) = ""
Next
.Value = WorksheetFunction.Trim(Join(Parts, " "))
.Offset(, 1).Value = Text
End With
Next
End With
End Sub
--
Rick (MVP - Excel)
"Rick Rothstein" wrote in message
...
Give this macro a try (after assigning your actual settings to the three
Const statements)...
Sub ParseComments()
Dim X As Long, Z As Long
Dim DataLastRow As Long
Dim Text As String
Dim Parts() As String
Const DataStartRow As Long = 2
Const DataColumn As String = "A"
Const WorksheetName As String = "Sheet1"
With Worksheets(WorksheetName)
DataLastRow = .Cells(.Rows.Count, DataColumn).End(xlUp).Row
For X = DataStartRow To DataLastRow
With .Cells(X, DataColumn)
Parts = Split(Replace(.Value, "}", "{"), "{")
For Z = 1 To UBound(Parts) Step 2
.Offset(, (Z + 1) / 2).Value = Parts(Z)
Parts(Z) = ""
Next
.Value = WorksheetFunction.Trim(Join(Parts, " "))
End With
Next
End With
End Sub
--
Rick (MVP - Excel)
"astridc" wrote in message
...
Hello!
I have a column with text in cells; the text in each cell contains one,
more
or no comments. All comments are between {}.
I need to find text between { } that exists in a cell, cut it from that
cell
and paste it in the adiacent cell in the next column.
for example, I have the following text in cell A1:
"This is {comment1} some example {comment2} text"
I need to cut {comment1} and {comment2} from cell A1 and paste them both
in
cell B1, so that in column A I would only have the text without comments
and
in column B only the comments.
This would need to go on automatically for the whole column.
thank you all in advace for any suggestions!
AstridC
|