numbers formatted as text

W

willemeulen

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?:confused:
 
G

Gord Dibben

Phone numbers are generally textual.

Why would you want them to be numbers?

You want to calculate with them?


Gord Dibben MS Excel MVP
 
P

Pecoflyer

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 :)
 
W

willemeulen

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
 
W

willemeulen

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
 
W

willemeulen

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?

:confused:
 
P

Pecoflyer

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?
 
W

willemeulen

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|
+-------------------------------------------------------------------+
 
P

Pecoflyer

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|
+-------------------------------------------------------------------+
 
W

willemeulen

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:)
 
Top