| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Cell Format - What's wrong?
Hi,
I downloaded an excel spreadsheet from a website. Column A:B on the spreadsheet is a reference for my vlookup formula in another worksheet. The cell where my formula returns #N/A. Say Column A5 reflects 2000.01 If I go to the specific cell (A5) and "manually type" the exact information on the same cell (2000.01) then my formula will work! After typing manually and my formula works referring to A5 I tried use Column A5 as a base and use Format painter for the rest of Column A but all the rows in column A does not work for my formula unless I type manually each information on the cells in column A. Please help. |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Cell Format - What's wrong?
Try a few functions that'll describe that cell (before you retype the value).
=istext(a1) =isnumber(a1) =len(a1) My bet is that you're pasting extra characters into that cell--maybe extra spaces or those HTML non-breaking spaces (char(160)'s). Depending on what you find out, ... David McRitchie has a macro that can help clean this: http://www.mvps.org/dmcritchie/excel/join.htm#trimall (look for "Sub Trimall()") And if you're new to macros, you may want to read David's intro: http://www.mvps.org/dmcritchie/excel/getstarted.htm Danny wrote: Hi, I downloaded an excel spreadsheet from a website. Column A:B on the spreadsheet is a reference for my vlookup formula in another worksheet. The cell where my formula returns #N/A. Say Column A5 reflects 2000.01 If I go to the specific cell (A5) and "manually type" the exact information on the same cell (2000.01) then my formula will work! After typing manually and my formula works referring to A5 I tried use Column A5 as a base and use Format painter for the rest of Column A but all the rows in column A does not work for my formula unless I type manually each information on the cells in column A. Please help. -- Dave Peterson |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Cell Format - What's wrong?
You are absolutely right Mr. Peterson. Some are text, some are numbers but
most importantly, using =len(a1:a200), some rows comes with a different number! I'll go to the website you provided and hopefully, I'll be able to "clean-up" the worksheet. Thanks a lot. "Dave Peterson" wrote: Try a few functions that'll describe that cell (before you retype the value). =istext(a1) =isnumber(a1) =len(a1) My bet is that you're pasting extra characters into that cell--maybe extra spaces or those HTML non-breaking spaces (char(160)'s). Depending on what you find out, ... David McRitchie has a macro that can help clean this: http://www.mvps.org/dmcritchie/excel/join.htm#trimall (look for "Sub Trimall()") And if you're new to macros, you may want to read David's intro: http://www.mvps.org/dmcritchie/excel/getstarted.htm Danny wrote: Hi, I downloaded an excel spreadsheet from a website. Column A:B on the spreadsheet is a reference for my vlookup formula in another worksheet. The cell where my formula returns #N/A. Say Column A5 reflects 2000.01 If I go to the specific cell (A5) and "manually type" the exact information on the same cell (2000.01) then my formula will work! After typing manually and my formula works referring to A5 I tried use Column A5 as a base and use Format painter for the rest of Column A but all the rows in column A does not work for my formula unless I type manually each information on the cells in column A. Please help. -- Dave Peterson |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Cell Format - What's wrong?
Use a difference cell for each of the =len() formulas.
And only use a single cell in that formula: =len(a1) =len(a2) ..... Danny wrote: You are absolutely right Mr. Peterson. Some are text, some are numbers but most importantly, using =len(a1:a200), some rows comes with a different number! I'll go to the website you provided and hopefully, I'll be able to "clean-up" the worksheet. Thanks a lot. "Dave Peterson" wrote: Try a few functions that'll describe that cell (before you retype the value). =istext(a1) =isnumber(a1) =len(a1) My bet is that you're pasting extra characters into that cell--maybe extra spaces or those HTML non-breaking spaces (char(160)'s). Depending on what you find out, ... David McRitchie has a macro that can help clean this: http://www.mvps.org/dmcritchie/excel/join.htm#trimall (look for "Sub Trimall()") And if you're new to macros, you may want to read David's intro: http://www.mvps.org/dmcritchie/excel/getstarted.htm Danny wrote: Hi, I downloaded an excel spreadsheet from a website. Column A:B on the spreadsheet is a reference for my vlookup formula in another worksheet. The cell where my formula returns #N/A. Say Column A5 reflects 2000.01 If I go to the specific cell (A5) and "manually type" the exact information on the same cell (2000.01) then my formula will work! After typing manually and my formula works referring to A5 I tried use Column A5 as a base and use Format painter for the rest of Column A but all the rows in column A does not work for my formula unless I type manually each information on the cells in column A. Please help. -- Dave Peterson -- Dave Peterson |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Cell Format - What's wrong?
Use a _different_ cell for each of the =len() formulas.
(stupid fingers!) Dave Peterson wrote: Use a difference cell for each of the =len() formulas. And only use a single cell in that formula: =len(a1) =len(a2) .... Danny wrote: You are absolutely right Mr. Peterson. Some are text, some are numbers but most importantly, using =len(a1:a200), some rows comes with a different number! I'll go to the website you provided and hopefully, I'll be able to "clean-up" the worksheet. Thanks a lot. "Dave Peterson" wrote: Try a few functions that'll describe that cell (before you retype the value). =istext(a1) =isnumber(a1) =len(a1) My bet is that you're pasting extra characters into that cell--maybe extra spaces or those HTML non-breaking spaces (char(160)'s). Depending on what you find out, ... David McRitchie has a macro that can help clean this: http://www.mvps.org/dmcritchie/excel/join.htm#trimall (look for "Sub Trimall()") And if you're new to macros, you may want to read David's intro: http://www.mvps.org/dmcritchie/excel/getstarted.htm Danny wrote: Hi, I downloaded an excel spreadsheet from a website. Column A:B on the spreadsheet is a reference for my vlookup formula in another worksheet. The cell where my formula returns #N/A. Say Column A5 reflects 2000.01 If I go to the specific cell (A5) and "manually type" the exact information on the same cell (2000.01) then my formula will work! After typing manually and my formula works referring to A5 I tried use Column A5 as a base and use Format painter for the rest of Column A but all the rows in column A does not work for my formula unless I type manually each information on the cells in column A. Please help. -- Dave Peterson -- Dave Peterson -- Dave Peterson |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Cell Format - What's wrong?
There is another trick to this. Copy the entire column of numbers into a
notepad file and save it (as a txt). Then from Excel, open the txt file. These numbers will all be in number format and you can paste it over your original data. "Dave Peterson" wrote: Use a _different_ cell for each of the =len() formulas. (stupid fingers!) Dave Peterson wrote: Use a difference cell for each of the =len() formulas. And only use a single cell in that formula: =len(a1) =len(a2) .... Danny wrote: You are absolutely right Mr. Peterson. Some are text, some are numbers but most importantly, using =len(a1:a200), some rows comes with a different number! I'll go to the website you provided and hopefully, I'll be able to "clean-up" the worksheet. Thanks a lot. "Dave Peterson" wrote: Try a few functions that'll describe that cell (before you retype the value). =istext(a1) =isnumber(a1) =len(a1) My bet is that you're pasting extra characters into that cell--maybe extra spaces or those HTML non-breaking spaces (char(160)'s). Depending on what you find out, ... David McRitchie has a macro that can help clean this: http://www.mvps.org/dmcritchie/excel/join.htm#trimall (look for "Sub Trimall()") And if you're new to macros, you may want to read David's intro: http://www.mvps.org/dmcritchie/excel/getstarted.htm Danny wrote: Hi, I downloaded an excel spreadsheet from a website. Column A:B on the spreadsheet is a reference for my vlookup formula in another worksheet. The cell where my formula returns #N/A. Say Column A5 reflects 2000.01 If I go to the specific cell (A5) and "manually type" the exact information on the same cell (2000.01) then my formula will work! After typing manually and my formula works referring to A5 I tried use Column A5 as a base and use Format painter for the rest of Column A but all the rows in column A does not work for my formula unless I type manually each information on the cells in column A. Please help. -- Dave Peterson -- Dave Peterson -- Dave Peterson |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Cell Format - What's wrong?
I went to: http://www.mvps.org/dmcritchie/excel/join.htm#trimall
(look for "Sub Trimall()") The Trim All macro did it! Thank you for your help and tips! "Poorvi" wrote: There is another trick to this. Copy the entire column of numbers into a notepad file and save it (as a txt). Then from Excel, open the txt file. These numbers will all be in number format and you can paste it over your original data. "Dave Peterson" wrote: Use a _different_ cell for each of the =len() formulas. (stupid fingers!) Dave Peterson wrote: Use a difference cell for each of the =len() formulas. And only use a single cell in that formula: =len(a1) =len(a2) .... Danny wrote: You are absolutely right Mr. Peterson. Some are text, some are numbers but most importantly, using =len(a1:a200), some rows comes with a different number! I'll go to the website you provided and hopefully, I'll be able to "clean-up" the worksheet. Thanks a lot. "Dave Peterson" wrote: Try a few functions that'll describe that cell (before you retype the value). =istext(a1) =isnumber(a1) =len(a1) My bet is that you're pasting extra characters into that cell--maybe extra spaces or those HTML non-breaking spaces (char(160)'s). Depending on what you find out, ... David McRitchie has a macro that can help clean this: http://www.mvps.org/dmcritchie/excel/join.htm#trimall (look for "Sub Trimall()") And if you're new to macros, you may want to read David's intro: http://www.mvps.org/dmcritchie/excel/getstarted.htm Danny wrote: Hi, I downloaded an excel spreadsheet from a website. Column A:B on the spreadsheet is a reference for my vlookup formula in another worksheet. The cell where my formula returns #N/A. Say Column A5 reflects 2000.01 If I go to the specific cell (A5) and "manually type" the exact information on the same cell (2000.01) then my formula will work! After typing manually and my formula works referring to A5 I tried use Column A5 as a base and use Format painter for the rest of Column A but all the rows in column A does not work for my formula unless I type manually each information on the cells in column A. Please help. -- Dave Peterson -- Dave Peterson -- Dave Peterson |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Date in wrong format | Excel Discussion (Misc queries) | |||
| Insert Calculated Field (wrong Qty*Price = wrong Amount) | Excel Discussion (Misc queries) | |||
| Excel mailing list in wrong format for mail merge | Excel Discussion (Misc queries) | |||
| Wrong date format in header | Excel Discussion (Misc queries) | |||
| YYYY format displaying wrong date | Excel Discussion (Misc queries) | |||
Linear Mode
