importing phone numbers
Hi
I am trying to import a large phone list from a csv file, but when i do this it drop the first 0 in the number.
Is there a way to stop this happening?
Any help appreciated
Dave
Best Answer
-
It would look similar to the flagging formula above. If it is only dropping one zero then it would be as straightforward as
=IF(LEN([Number Column Name]@row) <> 11, "0" + [Number Column Name]@row)
If you are dropping multiple digits, then we would need to tweak it a little bit.
Answers
-
-
Hi Paul
If i change the field to a text file it drops the 0
Is there a way in Smart sheet to ensure a column is 11 digits, so if i import and it only gives me 10 digits it can auto add a zero at the beginning?
Thanks
Dave
-
Not a text file. The field itself in the csv file should be set as a text value.
There is no way to automatically add the zero upon import, but you could (after import) add a helper column (flag type in this example) and quickly apply the following column formula:
=IF(LEN([Number Column Name]@row) <> 11, 1)
This will flag all rows that are not 11 digits. You can then filter on the flag so that you only see the problem rows and can correct.
There is also a solution with a formula added (after import) that will fill those missing digits in with zero which you can then copy/paste special or just reference the new column in any calculations or hide the original column from view.
-
Hi Paul
Thanks for taking the time to answer, you mention a formula after import that will fill the missing digits with a zero. Could you kindly explain or provide a sample of what this would look like?
Thanks as always
Dave
-
It would look similar to the flagging formula above. If it is only dropping one zero then it would be as straightforward as
=IF(LEN([Number Column Name]@row) <> 11, "0" + [Number Column Name]@row)
If you are dropping multiple digits, then we would need to tweak it a little bit.
-
Thank you as always Paul
Most helpful
Regards
Dave
-
