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]

Can you please provide a couple of scenarios as well as what exact formula you are using, along with their current outputs? I think the problem might be that you are directly adding ", (" and "), " through concatenation. We may need to build a big formula to handle this.

 

[jiggly]

i am adding those values directly through concatenation

both of the following formulas are on Sheet2 in cell A1 and A2:

=replacebold('Sheet1'!G15)&", ("&'Sheet1'!C15&"), "&'Sheet1'!E15

=replacebold('Sheet1'!G16)&", ("&'Sheet1'!C16&"), "&'Sheet1'!E16

values on Sheet1 are as follows:

G15 test (not bold) C15 123456 E15 123456
G16 test (bold) C16 (no data) E16 (no data)

output is

A1: test, (123456), 123456
A2: , (),

 

[jasonb75]

MrKowz may have a better alternative, but try

=IF(replacebold(Sheet1!G16)="","",Sheet1!G16&IF(Sheet1!C16,", ("&Sheet1!C16&")","")&IF(Sheet1!E16,", "&Sheet1!E16,""))

I was going to suggest using conditional formatting to hide the bold cells, (it can be done without vba) but then you brought up concatenating several cells, so that wouldn't work as the underlying content would still be there.

 

[jiggly]

i get #VALUE! errors if i use that code

 

[jasonb75]

In that case try

=IF(replacebold(Sheet1!G16)="","",Sheet1!G16&IF(Sheet1!C16<>"",", ("&Sheet1!C16&")","")&IF(Sheet1!E16<>"",", "&Sheet1!E16,""))

 

[jiggly]

Now that works!
Thank you.

 

[jasonb75]

You're welcome, it might need tweaking a little,

I've gone with the basis that if G16 is bold then ignore any data in C16 or E16 and return a blank cell.

If G16 is not bold then the output will vary depending on the population of the other 2 cells, please check with, both empty, both populated, and both combinations of 1 empty, 1 polulated to ensure all give the desired result.

 

[jiggly]

it works correctly, now if my boss would just stopp adding requirements...lol

 

[jasonb75]

it works correctly, now if my boss would just stopp adding requirements...lol

but now you solved this your boss is only going to give you a big pile of near impossible tasks to complete

 

[jiggly]

If only I had solved it...lol

I had nothing to do with the solving part