+ Reply to Thread
Results 1 to 12 of 12

Formatting issue

  1. #1
    Forum Contributor
    Join Date
    02-17-2013
    Location
    israel
    MS-Off Ver
    Excel 2003
    Posts
    106

    Formatting issue

    Please see attached spreadsheet

    I cannot for the life of me figure out what is causing the #N/A errors to start in line 2... the formulas work fine as they are returning results in the first line... i have tried reformatting the cells, to everything possible!

    When i copy the numbers into the cells it happens, when i write the numbers in manually all is good. I have checked and there is no spaces before or after the numbers in the cell


    Anyone have any ideas that can help me out ?
    Attached Images Attached Images

  2. #2
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: Formatting issue

    Looks like the numbers in A3 and down are not really numbers, they are "numbers stored as text"

    Try copying a blank cell
    Highlight column A
    Right Click - Paste Special - Values - Add - OK

  3. #3
    Forum Contributor
    Join Date
    02-17-2013
    Location
    israel
    MS-Off Ver
    Excel 2003
    Posts
    106

    Re: Formatting issue

    Quote Originally Posted by Jonmo1 View Post
    Looks like the numbers in A3 and down are not really numbers, they are "numbers stored as text"

    Try copying a blank cell
    Highlight column A
    Right Click - Paste Special - Values - Add - OK
    Jonmo1

    Thanks however i cannot seem to get that to do anything... Any other ideas ?

  4. #4
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: Formatting issue

    Can you attach a sample book (pictures don't really help much) ?

  5. #5
    Forum Contributor
    Join Date
    02-17-2013
    Location
    israel
    MS-Off Ver
    Excel 2003
    Posts
    106

    Re: Formatting issue

    I know that they do not help... however i am not managing to upload the test book.... is there anything else that you can recommend that i try that might help... is there a way to hard reformat cells or contents of cells ? i have a feeling that the numbers are not being recognized as numbers and therefore the Vlookups are returning #N/A values... any way to trouble shoot this without a workbook ?

  6. #6
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: Formatting issue

    Yes, given the only information I have is a picture...It appears the issue is that the numbers are not really numbers.
    Because you can see A2 is RIGHT alligned (usually means it's a number)
    But A3 and down are LEFT alligned (usually means it's text)

    The solution I provided should work.

    Can you highlight one of the problematic 'numbers', press F2, then press Enter.
    Does that work?

  7. #7
    Forum Contributor
    Join Date
    02-17-2013
    Location
    israel
    MS-Off Ver
    Excel 2003
    Posts
    106

    Re: Formatting issue

    Great!

    Is there a way to do that in bulk ? i have hundreds of lines like that....

  8. #8
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: Formatting issue

    Yes

    Copy a blank cell

    Highlight column A
    Right Click - Paste Special - Values - Add - OK

    The ADD part is critical.

  9. #9
    Forum Contributor
    Join Date
    02-17-2013
    Location
    israel
    MS-Off Ver
    Excel 2003
    Posts
    106

    Re: Formatting issue

    What is add?
    Where is it?

  10. #10
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: Formatting issue

    After you copy the blank cell.
    Highlight column A
    Right Click and choose Paste Special

    In the dialogue window that pops up, you have 2 sections filled with options to choose from.
    In the first section called "Paste", you have All / Formulas / Values / Formats / Commetns etc....
    Choose Values.

    In the 2nd section called "Operation", you have None / Add / Subtract / Multiply / Divide
    Choose Add

    Click OK.


    These instructions are directed toward Excel 2003 (which your profile says you're using).
    But the picture you posted looks more like XL 2007+
    If you're actually using XL 2007 or greater, then it's slightly different.

    In XL2007+, when you hover your mouse over Paste Special, they put in this stupid little window that slides out to the right.
    You don't have all those options in that little slide out window (one of the things I absolutely hate in 2007+)
    Instead of just letting that window slide out to the right.
    Actually physically click your mouse where it says Paste Special.
    Then you get the old 2003 dialogue window with all the options.
    Last edited by Jonmo1; 02-12-2014 at 12:19 PM.

  11. #11
    Forum Contributor
    Join Date
    02-17-2013
    Location
    israel
    MS-Off Ver
    Excel 2003
    Posts
    106

    Re: Formatting issue

    Thanks!!!

    That worked great !!!

  12. #12
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Formatting issue

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.
    If I have helped, Don't forget to add to my reputation (click on the star below the post)
    Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
    Use code tags when posting your VBA code: [code] Your code here [/code]

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Formatting Issue
    By rwmeis in forum Excel General
    Replies: 4
    Last Post: 11-19-2013, 01:28 PM
  2. Formatting issue
    By TheSneakiness in forum Excel General
    Replies: 1
    Last Post: 10-03-2012, 12:52 AM
  3. formatting issue
    By ashw1984 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 01-23-2009, 05:32 AM
  4. Formatting Issue
    By rolltyde12 in forum Excel General
    Replies: 1
    Last Post: 03-20-2007, 04:20 PM
  5. Formatting issue
    By adin in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 04-22-2005, 02:06 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1