Cut paste anything that is not bold

[excelvbanoob420]

Hello,

I need help with a macro which will cut & paste the text to the next column if its not bold as shown below:

Input:

ID	Type	Text
1​	Fruit	Lorem Ipsum
2​	Vegetables	Contrary to popular belief,
3​	Fruit	going to use a passage
4​	Fruit	Lorem Ipsum generators on the Internet
5​	Vegetables	res, to generate Lorem Ipsum
6​	Vegetables	for those interested
7​	Vegetables	going through the cites of the word
8​	Vegetables	chunk of Lorem Ipsum used
9​	Vegetables	generator on the Internet

Output:
1. Change the column name from Text -> Text format
2. Create a new column called 'Parsed Text' which will have the text that is not bold and the bold items will be replaced with Fixedtext.

ID	Type	Text format	Parsed Text
1​	Fruit	Lorem Ipsum	Fixedtext
2​	Vegetables		Contrary to popular belief,
3​	Fruit		going to use a passage
4​	Fruit	Lorem Ipsum generators on the Internet	Fixedtext
5​	Vegetables	res, to generate Lorem Ipsum	Fixedtext
6​	Vegetables		for those interested
7​	Vegetables	going through the cites of the word	Fixedtext
8​	Vegetables		chunk of Lorem Ipsum used
9​	Vegetables	generator on the Internet	Fixedtext

Thanks in advance

 

[excelvbanoob420]

Hello,

I need help with a macro which will cut & paste the text to the next column if its not bold as shown below:

Input:

ID	Type	Text
1​	Fruit	Lorem Ipsum
2​	Vegetables	Contrary to popular belief,
3​	Fruit	going to use a passage
4​	Fruit	Lorem Ipsum generators on the Internet
5​	Vegetables	res, to generate Lorem Ipsum
6​	Vegetables	for those interested
7​	Vegetables	going through the cites of the word
8​	Vegetables	chunk of Lorem Ipsum used
9​	Vegetables	generator on the Internet

Output:
1. Change the column name from Text -> Text format
2. Create a new column called 'Parsed Text' which will have the text that is not bold and the bold items will be replaced with Fixedtext.

ID	Type	Text format	Parsed Text
1​	Fruit	Lorem Ipsum	Fixedtext
2​	Vegetables		Contrary to popular belief,
3​	Fruit		going to use a passage
4​	Fruit	Lorem Ipsum generators on the Internet	Fixedtext
5​	Vegetables	res, to generate Lorem Ipsum	Fixedtext
6​	Vegetables		for those interested
7​	Vegetables	going through the cites of the word	Fixedtext
8​	Vegetables		chunk of Lorem Ipsum used
9​	Vegetables	generator on the Internet	Fixedtext

Thanks in advance

Also, one more thing the macro should work irrespective of the number of rows

 

[Alex Blakenburg]

Give this a try.
It will create a new sheet with the original name and a number after it.

Sub MoveUnbolded()

    Dim shtOrig As Worksheet, shtNew
    Dim lrowNew As Long, irow As Long
    
    Set shtOrig = ActiveSheet
    shtOrig.Copy after:=shtOrig
    Set shtNew = ActiveSheet
    
    With shtNew
        lrowNew = .Cells(Rows.Count, "C").End(xlUp).Row
    End With

    For irow = 2 To lrowNew
        With shtNew.Range("C" & irow)
            If .Font.Bold = False Then
                .Offset(, 1).Value = .Value
                .ClearContents
            Else
                .Offset(, 1).Value = "FixedText"
            End If
        End With
    Next irow
    
    With shtNew.Range("C1")
         .Value = "Text format"
        .Offset(, 1).Value = "Parsed Text"
        .Offset(, 1).EntireColumn.AutoFit
    End With

End Sub

 

[bebo021999]

Or, overwrite exist data:

Option Explicit
Sub test()
Dim cell As Range
For Each cell In Range("C2:C" & Cells(Rows.Count, "C").End(xlUp).Row)
    If Not cell.Font.Bold Then
        cell.Cut cell.Offset(, 1)
    Else: cell.Offset(, 1).Value = "Fixed Text"
    End If
Next
End Sub