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.
Awesome! Thanks Tom!
My pleasure Lukas, thanks for the comment!
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 – http://www.asap-utilities.com/ which can do the same and much more. Tony
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.
Very good tip Tom. Much appreciated.
Thanks Mike!
I Have a Question ?
How can i increase Row height in a cumulatively manner like row no. 2, 6, 10, 14, 18, 22 etc.
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?
Oh its very nice
Hi,
I want to prefix text from a cell to another cell which already has text in it, struggling
Please explain an example.
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
thanks! this helped a bunch. had to use # instead of @ as my column had only numbers but this worked!
Thanks for letting us know it worked for you!
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!!!!
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.
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.
Is there a way to paste cells with a custom format as text format? Where it keeps whatever custom formatting you used? For instance, I had phone numbers typed as ###-###-####. But I needed to change the formatting to be (###)###-####, without a space after the second parenthesis. So I used find and search to remove all the dashes, then used that custom formatting to change my phone numbers. This sheet is being used as import into a program and all cells need to be text format, but I can’t simply paste the value without losing my formatting. Am I just not doing something correctly, or is it even possible to do this? Any help would be appreciated. Thank you.
Not sure I completely follow, but would it help if when you copy the cell that has the custom format you want, you not just paste, but Paste Special for formats? If you want a value in that cell to also be involved, then Paste Special again for values.
Hi Tom,
Could the prefix “Widget Item” be a cell reference instead and the cell could be changed from time to time as necessary
I cannot say for sure unless you can offer more detail to what you are thinking. It might require VBA and if so, you would want to think about whether or not it is worth it to go to that extent.
Hi Tom,
Just came across this page and it was good help. Question: I have cell format (custom) done like this \”@\” to have the cell content enclosed by ” “. Begin and end quotes. It works for all except for contents more than 255 chars. Is there any solution to this? If the contents are more than 255 then the whole text is displayed like ############## and if the contents are <= 255 then it displays like "abc this is sample text".
Thanks a lot.
Vijay
Well, you did say any solution, so there are at least 2 options, because you are right about encountering a limitation in Excel for custom formats.
One option is a VBA Change event that takes text of any length and surrounds it with the double-quote character. If you want that, then post back.
Another option is to enter the text as a formula, for example:
Suppose in cell D1 you want to show the lengthy text surrounded by quotes.
Enter the text string in an unformatted cell, say D4.
In cell D1 enter this formula:
=CHAR(34)&D4&CHAR(34)
Now you can copy and paste special for values cell D1, or simply select cell D1, and hit F2 > F9 > Enter.
Hi Tom,
Thanks a lot for quick reply. Your suggested solution works fine for me (with some manual steps – but I am fine with that).
Best Regards,
Vijay