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

custom-format-to-catch-numbers-appearing-as-text

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 http://www.facebook.com/office

    Cheers,
    Andy
    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 :-)

Subscribe

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

Translate

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


ARCHIVES