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

The below macro should do this.

If you are new to macros --Set the Security level to low/medium in
(Tools|Macro|Security). 'Launch VBE using short-key Alt+F11. Insert a module
and paste the below code. Save. Get back to Workbook. Tools|Macro|Run Macro1()


Sub Macro1()

Dim intRow, strTemp, strData, arrData
intRow = 1

Do While Trim(Range("A" & intRow)) < ""
strData = Trim(Trim(Range("A" & intRow)))

Do
strTemp = strData
strData = Replace(strData, "{ ", "{")
strData = Replace(strData, " }", "}")
If strTemp = strData Then Exit Do
Loop

arrData = Split(strData, " ")
strTemp = ""
strData = ""
For intTemp = 0 To UBound(arrData)
If Left(arrData(intTemp), "1") = "{" Then
strData = strData & arrData(intTemp) & vbLf
Else
strTemp = strTemp & arrData(intTemp) & " "
End If
Next

Range("B" & intRow) = Trim(strTemp)
Range("C" & intRow) = Trim(strData)

intRow = intRow + 1
Loop

End Sub

If this post helps click Yes
---------------
Jacob Skaria

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

I wrote this code but the REPLACE functoin doesn't seem to work properly.
Can anybody see what is wrong?

Sub RemoveComments()

CellData = "This is {comment1} some example {comment2} text"
RowCount = 1
Do While Range("A" & RowCount) < ""
Comments = ""
'CellData = Range("A" & RowCount)
StartChr = 1
Do While InStr(StartChr, CellData, "{") 0
First = InStr(StartChr, CellData, "{") + 1
Last = InStr(StartChr, CellData, "}") - 1
If Last First Then
Length = Last - First + 1
Comment = Mid(CellData, First, Length)
CellData = Replace(expression:=CellData, Find:=Comment, _
Replace:="", Start:=First, Count:=1, compa=vbTextCompare)
'move past closing bracket
msgbox(CellData)
stop
StartChr = First + 2

End If
Loop
Loop

End Sub


"astridc" wrote:

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

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

Hello Jacob,
thank you! when I try this macro however, it will only extract the "{" and
the first word of the comment, but the comments between {} have various
lenghts.


"Jacob Skaria" a scris:

The below macro should do this.

If you are new to macros --Set the Security level to low/medium in
(Tools|Macro|Security). 'Launch VBE using short-key Alt+F11. Insert a module
and paste the below code. Save. Get back to Workbook. Tools|Macro|Run Macro1()


Sub Macro1()

Dim intRow, strTemp, strData, arrData
intRow = 1

Do While Trim(Range("A" & intRow)) < ""
strData = Trim(Trim(Range("A" & intRow)))

Do
strTemp = strData
strData = Replace(strData, "{ ", "{")
strData = Replace(strData, " }", "}")
If strTemp = strData Then Exit Do
Loop

arrData = Split(strData, " ")
strTemp = ""
strData = ""
For intTemp = 0 To UBound(arrData)
If Left(arrData(intTemp), "1") = "{" Then
strData = strData & arrData(intTemp) & vbLf
Else
strTemp = strTemp & arrData(intTemp) & " "
End If
Next

Range("B" & intRow) = Trim(strTemp)
Range("C" & intRow) = Trim(strData)

intRow = intRow + 1
Loop

End Sub

If this post helps click Yes
---------------
Jacob Skaria

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

Hi Joel

I too noticed this. Replace do not work as expected when you use the start
argument. If you try Replace(Data,strOld,strNew) it works.

If this post helps click Yes
---------------
Jacob Skaria


"joel" wrote:

I wrote this code but the REPLACE functoin doesn't seem to work properly.
Can anybody see what is wrong?

Sub RemoveComments()

CellData = "This is {comment1} some example {comment2} text"
RowCount = 1
Do While Range("A" & RowCount) < ""
Comments = ""
'CellData = Range("A" & RowCount)
StartChr = 1
Do While InStr(StartChr, CellData, "{") 0
First = InStr(StartChr, CellData, "{") + 1
Last = InStr(StartChr, CellData, "}") - 1
If Last First Then
Length = Last - First + 1
Comment = Mid(CellData, First, Length)
CellData = Replace(expression:=CellData, Find:=Comment, _
Replace:="", Start:=First, Count:=1, compa=vbTextCompare)
'move past closing bracket
msgbox(CellData)
stop
StartChr = First + 2

End If
Loop
Loop

End Sub


"astridc" wrote:

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

Hi Joel

REPLACE function in VBA is slightly different from Excel. If we specify the
start in VBA REPLACE then the returning value will only return from the start
position as in example 3. 4th argument is the number of replacements to be
made....

Replace("alphabet", "bet", "hydro") would return "alphahydro"
Replace ("alphabet", "a", "e") would return "elphebet"
Replace("alphabet", "a", "e", 2) would return "lphebet"
Replace("alphabet", "a", "e", 1, 1) would return "elphabet"

If this post helps click Yes
---------------
Jacob Skaria


"Jacob Skaria" wrote:

Hi Joel

I too noticed this. Replace do not work as expected when you use the start
argument. If you try Replace(Data,strOld,strNew) it works.

If this post helps click Yes
---------------
Jacob Skaria


"joel" wrote:

I wrote this code but the REPLACE functoin doesn't seem to work properly.
Can anybody see what is wrong?

Sub RemoveComments()

CellData = "This is {comment1} some example {comment2} text"
RowCount = 1
Do While Range("A" & RowCount) < ""
Comments = ""
'CellData = Range("A" & RowCount)
StartChr = 1
Do While InStr(StartChr, CellData, "{") 0
First = InStr(StartChr, CellData, "{") + 1
Last = InStr(StartChr, CellData, "}") - 1
If Last First Then
Length = Last - First + 1
Comment = Mid(CellData, First, Length)
CellData = Replace(expression:=CellData, Find:=Comment, _
Replace:="", Start:=First, Count:=1, compa=vbTextCompare)
'move past closing bracket
msgbox(CellData)
stop
StartChr = First + 2

End If
Loop
Loop

End Sub


"astridc" wrote:

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