Tom’s Tutorials For Excel: Adding Text With a Custom Format

Tom’s Tutorials For Excel: Adding Text With a Custom Format

There are times when you want to append text to a cell’s existing text, without using a helper column with a concatenation formula, and without any programming methods.

As you know, formatting a cell only changes the visual appearance of the cell; the cell’s underlying actual value stays intact and unaffected. If the appended text is just for visual benefit as shown in the following pictured example, here is how you can utilize a custom format to get the job done. In the picture, the company wants “Widget Item” to appear in front of all its listed Product Items.

Step 1
Select the range of interest and right-click the mouse. From the popup menu, select Format Cells.

Step 2
In the Format Cells dialog box:
• Select the Number tab.
• In the Category pane select Custom.
• In the Type field, enter "Widget Item "@
• Click OK.

Share Button
Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , , ,
13 comments on “Tom’s Tutorials For Excel: Adding Text With a Custom Format
  1. Lukas says:

    Awesome! Thanks Tom!

  2. Tony says:

    Very good. I had not seen that before. You could of course be very lazy and get yourself a copy of the excellant ASAP Utilities – which can do the same and much more. Tony

    • Tom Urtis says:

      Thanks for your comment. I’m not picking on asap utilities, I’d say this about any add-in, and asap is a good one. The biggest problem with commercial add-ins is that if your project is being used by anyone other than you (generic “you”), that/ those other user(s) also need the add-in loaded onto their computers. Many companies’ IT departments don’t allow downloaded utilities. In fact, sometimes it’s a real battle between IT and management in companies before I get called, just for VBA and API. In my case, and others who also develop projects for clients, everything needs to be encased in the project or with a native Excel add-in for the client company to approve it. But if you are an independent Exceler, as millions are, the asap utility is quite good. I’m developing a few add-ins myself that I’ll be selling on my Store page soon, because there certainly is a market for them as you pointed out.

  3. Mike Klubok says:

    Very good tip Tom. Much appreciated.


    I Have a Question ?
    How can i increase Row height in a cumulatively manner like row no. 2, 6, 10, 14, 18, 22 etc.

    • Tom Urtis says:

      Row heights are a change to the physical environment of an Excel spreadsheet, as opposed to a custom format that only makes it *look* as if something changed.

      Several ways to go about this; are you looking for a manual method or a programmatic method?

  5. Faiz Khan says:

    Oh its very nice

  6. Lionel says:

    I want to prefix text from a cell to another cell which already has text in it, struggling

  7. Nedra says:

    thanks! this helped a bunch. had to use # instead of @ as my column had only numbers but this worked!

Leave a Reply

Your email address will not be published. Required fields are marked *