importing phone numbers

dave.mcpherson56751

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

Paul Newcome

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.

Paul Newcome

Make sure the field in the file is a text field.

dave.mcpherson56751

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

Paul Newcome

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.

dave.mcpherson56751

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

Paul Newcome

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.

dave.mcpherson56751

Thank you as always Paul

Most helpful

Regards

Dave

Paul Newcome

Happy to help. 👍️