Excel Custom Formatting Adding Characters and Text

When working with custom formats in Excel there may be times you want to add a specific character or text string before or after the cell value. I will be explain this using examples in this article.


Literal Characters:

There are certain characters that will be displayed exactly as they are in the custom format:

q w t u i o
p a f j k l
z x c v $ +
( : ^ { <
= / ) ! &
~ } > (Space)

These characters and their uppercase equivalent will be displayed as they are.

Example:

In this example the character “q” will be added to the end of the number:

0q

Result:

Excel, Custom Formatting, Text
Example:

In this example the character “a” will be added to the start of the text string:

a@

For more information about the “@” character please see the link below:

Result:

Excel, Custom Formatting, Text, Example 2


Backslash Character “”:

Another method for adding characters in custom formatting is using the backslash character. The character that comes after a backslash is displayed as it is in the custom formatting. I will explain this using examples:

Example:

In this example the characters “e” and “r” are added to the end of the number:

0er

Result:

Excel, Custom Formatting, Text, Example 3

Example:

In this example the characters “t”and “y” are added before the text in each cell:

ty@

For more information about the “@” character please see the link below:

Result:

Excel, Custom Formatting, Text, Example 4


Double Quotes:

Another method for adding text to custom formats is enclosing the text in double quotes. Again I will explain this using examples:

Example:

In this example the text ” Kg” will be added after the numbers:

0" Kg"

Result:

Excel, Custom Formatting, Text, Example 5

Example:

In this example the text “Dr ” will be added before the value in each cell:

"Dr "@

For more information about the “@” character please see the link below:

Result:

Excel, Custom Formatting, Text, Example 6

You can download the file related to this article from the link below

See also:

 

If you need assistance with your code, or you are looking for a VBA programmer to hire feel free to contact me. Also please visit my website  www.software-solutions-online.com

Leave a Reply

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