Custom field format

i have created a custom field for telephone number, formatted as a text field so that the leading 0 doesn’t get dropped., and placed it on the customer record. I noticed when doing a batch update that the leading zero has been dropped in the exported data. I haven’t re-imported the batch, as I don’t want to loose the leading zero’s from the phone numbers. Has anyone experience with this? Regards, Peter

I have not experienced this myself, but your problem almost certainly lines in formatting of the spreadsheet, which is interpreting what you think is text as numbers.

Your spreadsheet program recognised the phone number was a number then applied the spreadsheet number format.

Solutions

  • when importing the data into your spread sheet, format the phone number column as text not General

  • or in your spread sheet choose a format for the phone number which includes a fixed number of digits (this does not work if your phone numbers are not of fixed length)

Thanks Tut -
Interesting - the spreadsheet has allocated general format to the column, which has no specific number format yet it drops the leading zero.

The format is set by the batch update function - if I put a . in front of the phone no, that might keep the leading 0?

No, the change is done by your spreadsheet program when it processes the phone number field as a number.

To stop that you can

  • change the data in Manager so your spreadsheet does not recognise it is a number

  • Change how your spreadsheet processes that field during data import into the spreadsheet.

The phone number field in my customer record in Manager is an alpha field - I tried a number field, but that doesn’t accept a leading zero. Maybe it would work if I put an alpha character in front of the phone no - T 012 345 ?

I think the spreadsheet is formatted by the batch update function in manager, which creates the spreadsheet. If I change the format to number it doesn’t restore the 0. When the batch is re-imported it would change the phone numbers in the customer records.

I don’t think it is a manager issue - it is an xl problem, leading zero’s are difficult to express in xl.

Why not use a country code such as +44 (UK) in front?

correct.

That is the opposite to what I suggested.
In your spreadsheet you need to force the spreadsheet to import the phone number field as text NOT number.

To do the later when importing into the spreadsheet select a field type of text, NOT number or general.
To do the former enter other characters in the field so it becomes not recognisable as a number by your spreadsheet.

Why didn’t I think of that?
Thanks eko - problem solved!
Peter

1 Like

Then you should not be discussing it on the forum. You have your answer. I am closing this topic.