Replace bold with Blank

[jiggly]

I am looking for the proper syntax to replace any bold text with blank text...
I am currently using this:

=SUBSTITUTE('BOOK1'!G1, "some text", "")

It just replaces any instances of "some text" with nothing, and this works fine, however, i would like to make it a little more general.

Ideally, i would like to use replace any bold font with no text, but i can't figure out the syntax

=REPLACE('BOOK1'!G1.font.bold, "") or something like that

 

[MrKowz]

Welcome to the forums!

You'll need to use a VBA function for this, try the following Custom Function:

Public Function ReplaceBold(rng As Range)
Dim i   As Long
If Len(rng.Value) > 0 Then
    For i = 1 To Len(rng.Value)
        If Mid$(rng.Value, i, 1) <> " " Then
            If rng.Characters(Start:=i, Length:=1).Font.Bold = False Then
                ReplaceBold = ReplaceBold & Mid$(rng.Value, i, 1)
            End If
        Else
            ReplaceBold = ReplaceBold & " "
        End If
    Next i
End If
End Function

To use this, do the following:

1. Press Alt+F11 to open the VBA Editor
2. Insert>Module
3. Copy/paste the above code into the module
4. Go back to your worksheet and use the function =REPLACEBOLD(range)

 

[jiggly]

so there is no possible way to do it without VBA?

Thank you btw.

 

[MrKowz]

As far as I'm aware, no native excel formulas can look at the formatting of individual characters.

 

[jiggly]

worked fairly well, however, i got some fields blank, and others with a "0" in them afterward.... any ideas?

 

[MrKowz]

Can you please provide some examples of what didn't give the proper result, as well as what your desired result is? It seemed to work fine on my end.

 

[MrKowz]

I'm looking at your file now. For the fields that are blank, I'm assuming you want them blank, as the entire content of those fields is bold?

 

[jiggly]

Yes, all the fields that are blank should remain blank, and all fields that are bold should become blank as well....

I'm not sure why zeros are showing up at all....

 

[jiggly]

ok, i rebuilt the sheet from scratch, and am no longer having the problem.... some kind of formatting must have been stuck in there, but for the life of me, i couldn't find it.....
no worries..... I do have another function i would like to add however.

The output is exactly what I want, and manually, I can remove the blank rows, but when I try it with a macro, the result returns a #VALUE! error.

Why can i remove the blank rows manually, but not with a macro?

 

[MrKowz]

What is the macro you are trying to use to remove blank rows?