Categorized | excel tips

Custom Format to Left Align Text Symbol and Right Align Numbers

In Custom Format Numbers and Text we discussed using the custom format feature on numbers and text. Here is a neat little trick using which we can format a cell to make the text symbol align left and the numbers present in the cell right.

Hold your comes…


“Custom Text Symbol”* General

Custom Text Symbol is the customized symbol that you would like to appear against each number. So if you’ve got a numerical value and wanted to format it using a personalized symbol, this should do it for you.

custom-format-to-left-align-text-symbol-and-right-align-numbersLet’s take an example (I must admit that its not a very good one but nonetheless one that helps illustrate the concept). You are planning to buy a car and wanted to compare the engine capacity of various models on offer. What you can do is to write the custom format as “cc”* General. That way the cc (cubic centimeter) symbol stays towards the left and the number part (the engine capacity) stays towards right. Keep in mind the space between the asterix and the General tag. The * symbol repeats the immediate next character and fills up the remaining part of the cell with it. In our case, the immediate next character is the space and therefore the cell is filled up with spaces. The General tag at the end places the number at the extreme right.

And incase you are wondering how to get the symbol for a particular character, well head over to the menu and click “Insert” -> “Symbol”. You can select from a whole host of characters and symbols here. Once you insert it in a cell, you can copy the symbol (by pressing F2 and selecting it) and then using crtl+1 to open the custom format box. You can then paste the symbol along with the formula described above to create your very own custom string.


What Do You Think ?

XHTML: You can use these tags: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

Comments and Trackbacks

  1. Carolyn Marshall wrote:

    I need to have country names on the left and percentages on the right.

    So my text is not always the same.

    I followed your instructions just to see if I could make you example work, but I couldn’t get it to work.

  1. Eigenschappen TextBox - wrote:

    […] Custom Format to Left Align Text Symbol and Right Align Numbers | Excel & VBA – da Tab Is … het lijkt erop, dat dit een soort van speciale functionaliteit is, die niet zomaar in vba kan ik […]


Chinese (Simplified)DutchEnglishFilipinoFrenchGermanHebrewHindiItalianJapanesePortugueseRussianSpanish

treeemap software for excel