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
I'm confuesed, I see you done something but when I edit the cells to text or anything else nothing seems to work. Make them both text, both general, both numbers etc. Now I coppied your formula for the lost row. In my sheet it shows the formula without doing anything =VLOOKUP( etc What is happening in this sheet, corrupted? 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 |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
numbers formatted as text
Could it be that the formulae contain *.* instead of *,* ? -- 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 |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
numbers formatted as text
Formula's are fine, they always worked before without any problems. I went throuh it again, started a new workbook and imported my latest phone bill and same thing, refuses to lookup anything. The only thing I can think of is that my phone numbers imported from my outlook contact list have a bug, all where '0826526 etc. I removed the ' and 0 by hand for all of them, removed spaces, + etc to match values from phone bill. I did this with my last phone bill without any problems, only now I have a new updated contact list. Do you think there could be any hidden symbols in the numbers which bugger it up for me? -- 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 |
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
numbers formatted as text
Can you send a sheet with your raw data ( unchanged phone numbers)? Do your regional settings use a comma or a semi-colon to separate items in formulas? -- 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 |
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
numbers formatted as text
I attached the very raw data as I import contacts and phone bills, currently busy editing the contact to only a few columns. I always use , (Comma) in my formulas. Thanks for all your effort Pflyer! W +-------------------------------------------------------------------+ |Filename: export june 2009.xls | |Download: http://www.thecodecage.com/forumz/attachment.php?attachmentid=156| +-------------------------------------------------------------------+ -- 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 |
#13
Posted to microsoft.public.excel.misc
|
|||
|
|||
numbers formatted as text
Hope this will get you started First insert a new sheet (Cleaned contacts partial) and use the UDF hereafter to extract only digits from the phone numbers in col 1 to 4 in this new sheet Code: -------------------- =IF(ISERROR(personal.xls!DigitsDashesAll(Contacts! A2)+0);"";personal.xls!DigitsDashesAll(Contacts!A2 )+0) -------------------- Link the new sheet to the original one to retrieve whatever data needed Use VLOOKUP to retrieve your data in Sheet1 Code: -------------------- Function DigitsDashesAll(ByVal s As String) As String 'Harlan Grove, worksheet.functions, 2003-10-20 'concatenate all digits and dashes found in a string Dim i as long, n as long n = Len(s) For i = 1 To n If Mid(s, i, 1) Like "[!-0-9]" Then Mid(s, i, 1) = " " Next i DigitsDashesAll = Application.WorksheetFunction.Substitute(s, " ", "") End Function -------------------- Don't forget to replace semi-colons with commas where needed +-------------------------------------------------------------------+ |Filename: Copy of export june 2009-2.xls | |Download: http://www.thecodecage.com/forumz/attachment.php?attachmentid=157| +-------------------------------------------------------------------+ -- 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 |
#14
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 |