Deleting only bold text in a cell

FrankB

New Member
Joined
Sep 3, 2002
Messages
31
Is there a way to automate that? My cells have bold and normal font, and I would like to delete all the bold text.

Thanks,

Frank
 

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.

onlyadrafter

Well-known Member
Joined
Aug 19, 2003
Messages
5,703
Platform
  1. Windows
Hello,

Does this code suit your needs?

Code:
Sub remove_bold()
Set c = Selection
For Each cell In c
    MY_ROW = cell.Row
    MY_COLUMN = cell.Column
    NEW_TEXT = ""
    For MY_CHAR = 1 To Len(ActiveCell)
    NEW_CHAR = Mid(ActiveCell, MY_CHAR, 1)
    With ActiveCell.Characters(Start:=MY_CHAR, Length:=1).Font
        If .FontStyle <> "Bold" Then
            NEW_TEXT = NEW_TEXT + NEW_CHAR
        End If
    End With
    Next MY_CHAR
    Range("a1").Offset(MY_ROW - 1, MY_COLUMN - 1).Value = NEW_TEXT
Next cell
End Sub

There is probably a better way, but it seems to work.

You need to select the cells first then RUN the macro.

Just noticed this will not work if all the text in the cell in BOLD, but this code will

Code:
Sub remove_bold()
Set c = Selection
For Each cell In c
    MY_ROW = cell.Row
    MY_COLUMN = cell.Column
    If Range("a1").Offset(MY_ROW - 1, MY_COLUMN - 1).Font.FontStyle = "Bold" Then
        Range("a1").Offset(MY_ROW - 1, MY_COLUMN - 1).Font.FontStyle = "Regular"
    End If
    NEW_TEXT = ""
    For MY_CHAR = 1 To Len(ActiveCell)
    NEW_CHAR = Mid(ActiveCell, MY_CHAR, 1)
    With ActiveCell.Characters(Start:=MY_CHAR, Length:=1).Font
        If .FontStyle <> "Bold" Then
            NEW_TEXT = NEW_TEXT + NEW_CHAR
        End If
    End With
    Next MY_CHAR
    Range("a1").Offset(MY_ROW - 1, MY_COLUMN - 1).Value = NEW_TEXT
Next cell
End Sub

Is this OK?
 

Chitosunday

Well-known Member
Joined
Jul 14, 2003
Messages
1,017
Select the cells, columns or row you want to look then run this

Sub delbold()
For Each c In Selection
If c.Font.FontStyle = "Bold" Then c.Clear
Next c

End Sub
 

onlyadrafter

Well-known Member
Joined
Aug 19, 2003
Messages
5,703
Platform
  1. Windows
Hello,

Having read Chitosunday' reply, which clears cells if it is bold. I thought I'd better explain my reply.

My macro will remove BOLD text within a cell that has both REGULAR and BOLD text.

i.e.

Delete this BOLD TEXT

will change to

Delete this

I don't know which you require.



and Delete THIS BOLD Text

will change to

Delete Text
 

FrankB

New Member
Joined
Sep 3, 2002
Messages
31

ADVERTISEMENT

I want to delete bold text within a cell, not the whole text. I can't get your code to work though - I just copied it into a macro, and run it, but there are no changes. Do I need to set up something differently?

Thanks,

Frank
 

onlyadrafter

Well-known Member
Joined
Aug 19, 2003
Messages
5,703
Platform
  1. Windows
Hello,

For this to work you need to select the cells you wish to change and then run the macro. Is this what you did?
 

FrankB

New Member
Joined
Sep 3, 2002
Messages
31

ADVERTISEMENT

Yep.
 

FrankB

New Member
Joined
Sep 3, 2002
Messages
31
Ok, here is some new info: It does work in the Personal spreadsheet, but not anywhere else. Even if I copy it into another workbook, it doesn't work there. Any ideas?

Thanks,

Frank
 

FrankB

New Member
Joined
Sep 3, 2002
Messages
31
Also, if the text starts bold, it does remove that, but puts everything else in bold, too.

Frank
 

onlyadrafter

Well-known Member
Joined
Aug 19, 2003
Messages
5,703
Platform
  1. Windows
Hello,

Have changed the code, it seems to work for me

Code:
Sub remove_bold()
Set c = Selection
For Each cell In c
    MY_ROW = cell.Row
    MY_COLUMN = cell.Column
    If Range("a1").Offset(MY_ROW - 1, MY_COLUMN - 1).Font.FontStyle = "Bold" Then
        Range("a1").Offset(MY_ROW - 1, MY_COLUMN - 1).Clear
    End If
    NEW_TEXT = ""
    For MY_CHAR = 1 To Len(Range("A1").Offset(MY_ROW - 1, MY_COLUMN - 1))
    NEW_CHAR = Mid(Range("A1").Offset(MY_ROW - 1, MY_COLUMN - 1), MY_CHAR, 1)
    With Range("A1").Offset(MY_ROW - 1, MY_COLUMN - 1).Characters(Start:=MY_CHAR, Length:=1).Font
        If .FontStyle <> "Bold" Then
            NEW_TEXT = NEW_TEXT + NEW_CHAR
        End If
    End With
    Next MY_CHAR
    Range("a1").Offset(MY_ROW - 1, MY_COLUMN - 1).Value = NEW_TEXT
    Range("a1").Offset(MY_ROW - 1, MY_COLUMN - 1).Font.FontStyle = "Regular"
    NEW_TEXT = ""
Next cell
End Sub

Is this working OK for you?
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,152,096
Messages
5,768,061
Members
425,451
Latest member
JohnBrooksBiddle

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top