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: , , , , , , , , , , , , ,
17 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

    • Tom Urtis says:

      Please explain an example.

    • Philip Hinton says:

      I don’t think formatting will work for you Lionel.
      Assuming your two cells are A1 and B1, for example, you’ll probably need to concatenate them in a third cell with the delimiter being the bit between the quote marks:

      =A1&” “&B1

  7. Nedra says:

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

  8. James says:

    HI Tom, thanks for the tutorials!

    I’m using VBA to create several custom formats like your example here.

    Trying for one step further: Is it possible to give this added text its own font characteristics?
    For instance, I would like “Widget Item” to be normal font and “AB12CD” to be in BOLD to make it pop more for the user.

    In other instances, I would like to add a suffix to the cell contents but using subscript (or at least a much smaller font than the text/value entered).

    For the bolding example, I’ve tried the only thing I could find: ActiveCell.Characters(Start:=12, Length:=6).Font.FontStyle = “Bold”
    but it just ignores any .Characters changes, no matter what you do. It appears that the Custom format gets applied AFTER this VBA Character.Font change and changes it back.

    Any ideas?
    Thanks in advance!!!!

    • Tom Urtis says:

      Formatting a cell such as I showed in the pictures only affects the appearance of what you see visually when you look at the cell. Those extra characters are really not there; they literally do not physically exist. That is why your attempt to format them was unsuccessful, because there was nothing concrete to format.

      The alternative is to append actual true text to formula result, instead of formatted “ghost text”. If you do that, then yes you can format those characters in different ways in the same cell. You are on the right track with the syntax as you posted it. The issue is to have actual text be present on the cell upon which that code can and would act.

      • James says:

        Thanks for the quick reply. Twas as I suspected, back to using formula concatenation & partial formatting. Funny though that it won’t allow partial formatting on the real text portion simultaneously while adding the “ghost text”. You can start with a cell with partial formatting, but it will disappear as soon as you add the “Widget Item”@. Then set the number format back to General and the partial font formatting reappears! As far as MS quirks go (stating it nicely), this one is way out in left field somewhere, nothing to waste time and energy on. …as I muse to myself, glancing at the clock that shows 3am, knowing full well that I should’ve been asleep hours ago instead.

Leave a Reply

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