Categorized | excel tips

Custom Format to Catch Numbers Appearing as Text

When raw data (in text *txt or csv format) is imported to excel, the conversion can sometimes have an unintended side-effect – the numbers come out as text after the process. The only choice left is to identify the columns that contain numbers masquerading as text and then to use the text to column option on each of the columns one-by-one. If the number of columns is large, eyeballing the data can be a bit of a strain.

Here a quick tip to help with the process using custom format – simply select all the cells in the worksheet by :
1. Pressing ctrl + a (use that command twice if excel selects only the adjacent cells)
2. Then pressing ctrl + 1
3. enter [Red]@ in the ‘Format Cells’ -> Custom box


Any cells with text (as opposed to numbers) will now get highlighted in Red. Its now a fairly easy job to identify the columns which have numbers appearing as text and to then selectively use the text to column option.

Excel Formula, Excel Chart, Excel Macro, Excel VBA, Pivot Table Excel, Excel Dashboard

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. Andy wrote:

    Great tips! This is an awesome solution to a annoying problem. Keep up the fantastic posts!

    We’d also love to hear from you at

    MSFT Office Outreach

  2. Chandoo wrote:

    Very good tip Ajay… custom cell formatting codes are one of my favorite feature in Excel. They are very simple to use and yet very powerful….

  3. Ajay wrote:

    @Chandoo – Thanks 🙂

  4. Kanhaiyalal Newaskar. wrote:

    Awesome Notes and formula you shown in ARRAY FORMULA & DATE FORMULA. I and others too, would like to learn such items as and when you will provide.
    thanking you.

    Kanahiyalal Newaskar, Ahmednagar.(Maharashtra).


Keep up with the latest stories - Delivered right to your inbox


English flagItalian flagKorean flagChinese (Simplified) flagPortuguese flagGerman flagFrench flagSpanish flagJapanese flagArabic flagRussian flagDutch flagHindi flagFilipino flagIndonesian flagThai flagTurkish flagPersian flag
treeemap software for excel