Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
numbers formatted as text
I have imported my contacts from outlook into excel and having trouble with the phone numbers. for some reason sorting does not go as it should and a vlookup function does not retrieve anything but #N/A#. Looks like some of the numbers are still in text somehow, eventhough I selected all of the cells and put it to number, general.... nothing seems to help. Anybody know how to solve this problem? -- willemeulen Blink1 *Willem van der Meulen* BSc Soil & Water Eng (Netherlands) ------------------------------------------------------------------------ willemeulen's Profile: http://www.thecodecage.com/forumz/member.php?userid=285 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=104270 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
numbers formatted as text
first use isnumber(A1) to find cells with text. If the phone numbers are in
column A the put formula in column B and copy down column B. the find one of the cells that isn't a number. There is a white invisible charact in the cell. Go to F(x) and copy only 1 of the invisible characters by typing Cntl-C. Then go to worksheet menu Edit - Replace. Paste the character into the From box by typing Cnt-F. Then Replace All. At least one character should of been replaced. If there are still more non-numeric values repeat the process until all are gone. there are two or 3 different invisible characters that can cause this problem. "willemeulen" wrote: I have imported my contacts from outlook into excel and having trouble with the phone numbers. for some reason sorting does not go as it should and a vlookup function does not retrieve anything but #N/A#. Looks like some of the numbers are still in text somehow, eventhough I selected all of the cells and put it to number, general.... nothing seems to help. Anybody know how to solve this problem? -- willemeulen Blink1 *Willem van der Meulen* BSc Soil & Water Eng (Netherlands) ------------------------------------------------------------------------ willemeulen's Profile: http://www.thecodecage.com/forumz/member.php?userid=285 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=104270 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
numbers formatted as text
Phone numbers are generally textual.
Why would you want them to be numbers? You want to calculate with them? Gord Dibben MS Excel MVP On Sun, 7 Jun 2009 13:52:58 +0100, willemeulen wrote: I have imported my contacts from outlook into excel and having trouble with the phone numbers. for some reason sorting does not go as it should and a vlookup function does not retrieve anything but #N/A#. Looks like some of the numbers are still in text somehow, eventhough I selected all of the cells and put it to number, general.... nothing seems to help. Anybody know how to solve this problem? |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
numbers formatted as text
WHY NOT ADD A WORKBOOK? Providing a workbook will not only get you your answer quicker but will better illustrate your problem, usually when we can see your data (-it can be dummy data but must be of the same type-) and your structure it is far easier for us to give you a tailored, workable answer to your query :) -- Pecoflyer Cheers - ------------------------------------------------------------------------ Pecoflyer's Profile: http://www.thecodecage.com/forumz/member.php?userid=14 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=104270 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
numbers formatted as text
my biggest problem is that the vlookup is not working for some reason. Have no Idea why but suspect it has something to do with imported data not all being of the same type (text/number). Still trying, will upload workbook tomorrow if it still gives me problems w -- willemeulen Blink1 *Willem van der Meulen* BSc Soil & Water Eng (Netherlands) ------------------------------------------------------------------------ willemeulen's Profile: http://www.thecodecage.com/forumz/member.php?userid=285 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=104270 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
numbers formatted as text
Please find my workbook attached. Marked the trouble area in yellow. W +-------------------------------------------------------------------+ |Filename: may8-may9.xls | |Download: http://www.thecodecage.com/forumz/attachment.php?attachmentid=154| +-------------------------------------------------------------------+ -- willemeulen Blink1 *Willem van der Meulen* BSc Soil & Water Eng (Netherlands) ------------------------------------------------------------------------ willemeulen's Profile: http://www.thecodecage.com/forumz/member.php?userid=285 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=104270 |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
numbers formatted as text
Hi, your lookup in the May08 sheet are numbers, in the "contacts" sheet, text See attached file for a possible solution +-------------------------------------------------------------------+ |Filename: Copy of may8-may9.xls | |Download: http://www.thecodecage.com/forumz/attachment.php?attachmentid=155| +-------------------------------------------------------------------+ -- Pecoflyer Cheers - ------------------------------------------------------------------------ Pecoflyer's Profile: http://www.thecodecage.com/forumz/member.php?userid=14 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=104270 |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
numbers formatted as text
Thanks pecoflyer for all your effort. Am batteling with internet conenction today, keeps chucking me out. This is my third attempt on answering your last post. I prepared a new contact sheet from scratch and it works now, so the old sheet (worksheet) had a but in it which was causing all the ****. Thanks agian Pecoflyer. WBg:) -- willemeulen Blink1 *Willem van der Meulen* BSc Soil & Water Eng (Netherlands) ------------------------------------------------------------------------ willemeulen's Profile: http://www.thecodecage.com/forumz/member.php?userid=285 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=104270 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Pivot Table keep apart cells text that I've formatted as numbers | Excel Worksheet Functions | |||
formatted numbers displayed as ##### | New Users to Excel | |||
Sorting formatted numbers | Excel Discussion (Misc queries) | |||
Converting numbers formatted as text to numbers | Excel Discussion (Misc queries) | |||
Converting 'General' formatted cells to Text formatted cell using. | Excel Worksheet Functions |