How to add "empty" text to a cell already containing text?

[MikeO]

I don't think my title describes what I'm trying to do all that well so here is a more detailed description

In Column A, I have a list of people's names in the form of first letter of first name + last name (ex - Bsmith, MJohnson, AWilliams)

I have a number of sumproduct formulas throughout my spreadsheet that look at these names as part of the formulas.

However, some of the people on the list are only part time for the role I am analyzing. I would like to denote a "(Backup)" next to their names without changing the actual names in the cells so I don't have to rewrite all of my Sumproduct formulas.

What I normally do with text is go into Format - Cells and go down to custom and add a "backup" to the end of the cell so it would look like General "backup". But this seems to only work if what's in the cell is a number. When it's text like it is here, the "backup" doesn't show up in the cell.

Is there a way to add text to a cell that actually is just superficial so my formulas still only look at the cell as a name in the form of Bsmith?

Thanks.

 

[texasalynn]

you would have to put quotes around the text in the custom format, but I don't believe the sumproduct will see that in evaluating the contents

 

[MikeO]

That's the thing. I have quotes around the text but it only works if what's in the cell is a number and not text.

To test this, I created a list of 5 cells - some containing numbers and some containing words. I then formatted the cells putting a "test" with quotes in the format and only the cells with numbers added the "test" to the cell.

 

[texasalynn]

you will probably need to create a formula in a blank cell

=A1 & " backup"
copy this down and then you can do a copy - paste special value back over the original text

 

[MikeO]

That doesn't work - it views the cell as both the original text plus the (backup). There might not be a way to make this work without modifying all of the formulas

 

[wsjackman]

try formating the cell
custom ;;;@" (Backup)"

 

[MikeO]

That works, thanks.

 

[jim may]

What if the cell contains a number, like 123
and you want 123 (Backup) ?

 

[jim may]

Never mind, noticed that MikeO answered that in #1 as:
General "(Backup)"