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

MikeO

Board Regular
Joined
Jun 17, 2009
Messages
57
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.
 

Some videos you may like

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.

texasalynn

Well-known Member
Joined
May 19, 2002
Messages
8,458
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

Board Regular
Joined
Jun 17, 2009
Messages
57
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

Well-known Member
Joined
May 19, 2002
Messages
8,458
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

Board Regular
Joined
Jun 17, 2009
Messages
57

ADVERTISEMENT

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
 

MikeO

Board Regular
Joined
Jun 17, 2009
Messages
57

ADVERTISEMENT

That works, thanks.
 

jim may

Well-known Member
Joined
Jul 4, 2004
Messages
7,478
What if the cell contains a number, like 123
and you want 123 (Backup) ?
 

jim may

Well-known Member
Joined
Jul 4, 2004
Messages
7,478
Never mind, noticed that MikeO answered that in #1 as:
General "(Backup)"
 

Watch MrExcel Video

Forum statistics

Threads
1,122,469
Messages
5,596,315
Members
414,053
Latest member
Dual Showman

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