07-26-08 09:42 AM
- Strange problem, hope someone can help me with this....When exporting addresses into .csv format from a BB 8310 running 4.3 OS, there are equal signs in front of certain phone numbers, rendering the number as an equation in excel. I have exported addresses from from different BB devices and never experienced anything like this.
Does anyone know why this occurred and if so, how to correct without having to manually remove each = sign?...there are over 700 of them.
Ice07-25-08 01:01 PM
- jeffhRetired ModeratorWhen I export contacts, I export them from Outlook, not directly from the Blackberry. I just exported a file of 929 addresses in csv. Then I opened it in Excel. None of the phone numbers formatted as equations, and a search by Excel found no = signs.
How are you exporting your contacts?07-25-08 07:39 PM
- Not sure why you are getting the = sign. One question I would ask is do these numbers have a + sign in them in the BB address book. That may fool the .csv cell into thinking it is dealing with a formula. I've just never run into this specific condition so don't have direct experience.
Anyway, there is a way to fix this that is reasonably simple.
1. Sort the data, being SURE that all columns are selected so you don't scramble the data. Sort on the column that contains the phone numbers.
2. Use the LEFT(cell, len(cell)-x) command to extract the significant digits from the cell that has the =123..xx phone number. (The "len" command stands for "lenght" - or the length of the field you are trying to extract.) I'm not sure how many significant digits you have in these phone numbers -- 10 if it is a 1+area code+number, 9 if you don't have the 1 in the number, more if it is an international number, etc. You choose how many characters you want to extract from the cell counting right to left. (The LEFT command takes the x-number of Left-most characters from the target cell). You probably will want all the characters EXCEPT the = sign.
3. Use the VALUE command to create a numeric value from the cell you just created in step 2.
You now have a number that can be put into a phone number field in a PIM / contact list / Outlook etc. You can copy and paste this number back into the phone number field and you're good to go.
This may sound complicated, but it is really pretty straightforward. You only have to work to set up these steps on one row of data. Once you get the first line working right, just copy and paste those cells into all the rows that have phone numbers with =1223.xxx. (That's why you sorted the data -- to get all those numbers together.
JeffH's comments above are the way i usually do it as well, extracting / exporting out of Outlook. But, if you have no way to do that, and you have to deal with the data as you are getting it in the .csv file, there is an easy alternative to restroking all the data or editing every cell with an = sign.
Hope that helps.
(BTW -- Excel contains the instructions to do what I have suggested as well if you do a help search on Functions > Value and > LEN)
Last edited by Go Blue; 07-25-08 at 08:03 PM. Reason: clarification07-25-08 08:01 PM
LINK TO POST COPIED TO CLIPBOARD