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


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

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


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

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

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

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

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

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

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

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

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?

:confused:


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

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

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

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

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