Excel2007. importing phone# with leading zero. Zero gets dropped

[BoehmerR]

Hi,

I am downloading an online phone bill in a csv format. When opening the file, the phone numbers drop the leading 0. Example phone number is 0400123456.
This number is displaying as 400123456. I need this in a number format so I do not want to convert to text.
I know that if I can convert the format to 0400 123 456 (grouping the numbers with spaces between) then excel leaves the zero in place, but how do I do that without going into each cell individually and re typing it?
If I use custom format with a 0### ### ### then the formula vlookup will still read the cell as not having the 0 and i need to use vlookup.
My goal is to have a number format with the leading zero and grouped with #### ### ###
Help please as I am stuck

 

[Gerald Higgins]

Hi, welcome to the board.

Why do you need the telephone number in number format ?
Converting it to text sounds like an easy fix for your problem.
If you're not automatically getting the spaces to separate the number into chunks, there are various ways of doing it once you've imported the data.

For example, if you import the string "ABC" into cell A1, this formula will change it to "A B C"

=left(a1,1)&" "&mid(a1,2,1)&" "&right(a1,1)

 

[shg]

If I use custom format with a 0### ### ### then the formula vlookup will still read the cell as not having the 0 and i need to use vlookup.

Vlookup wll work fine if your numbers and the lookup table both use numbers, no matter how you format them (other than dates).

 

[BoehmerR]

Hi thanks for your reply.
unfortunately the table_array is number format and is #### ### ###.
When I do a vlookup it returns #N/A even if the range lookup is TRUE.
The only way I get the value I want is to have the same format (Number) and the same spacing.

 

[shg]

      -------A-------- --B--- C ------D------ --E--
  1      Formatted     Lookup   Not Formatted Name 
  2   +03 88 885 44 76 Ed          9180881160 Bob  
  3   +62 06 888 37 53 Chuck      62068883753 Chuck
  4   +09 18 088 11 60 Bob        62770820160 Dan  
  5   +62 77 082 01 60 Dan         3888854476 Ed   
  6   +94 34 407 47 72 Alan       94344074772 Alan

That's a range lookup of formatted numbers into a range of unformatted numbers and it works fine.

And vice-versa:

      -----A----- --B--- C -------D-------- --E--
  1    Formatted  Lookup    Not Formatted   Name 
  2    3888854476 Ed       +09 18 088 11 60 Bob  
  3   62068883753 Chuck    +62 06 888 37 53 Chuck
  4    9180881160 Bob      +62 77 082 01 60 Dan  
  5   62770820160 Dan      +03 88 885 44 76 Ed   
  6   94344074772 Alan     +94 34 407 47 72 Alan

 

[BoehmerR]

Thanks. This has worked. I used your suggestion, played a little and got the result I wanted. See my formula below. I used this on a text field then added the +0 to convert back to number format. Vlookup now works perfectly. You're a genius.

=LEFT(B11,4)&" "&MID(B11,5,3)&" "&(RIGHT(B11,3)+0)
changed text 0400123456 to Number 0400 123 456

Thank you.

 

[Gerald Higgins]

Just for the record, I don't think the +0 is actually doing anything.
I think you'll still be left with a text string.
+0 can, in some cases, force Excel to treat a text string as a number, but I don't think it's doing that in your example.

 

[BoehmerR]

You are correct. I found that it also dropped a 0 on numbers where a 0 appeared in the last group. e.g. #### ### 0## became #### ### ##?
I have removed the +0. The reformatting into a new cell with 'General' as the cell format type using your code worked. The vlookup works now. This will save me a lot of time. Thanks.