Help with leading zeros disappearing!

[sterjohn]

Hi, I am hoping someone can help me here?
I am trying to add text to the end of a list of mobile phone numbers in a column, (eg: 04555555555@xxxxxxxxxxx.com.au)
I have the mobile numbers in one column and @xxxxxxxxxxxx.com.au in another column.
When I try to combine the numbers and letters in a cell, the leading zero on the phone number disappears no matter what I try.
Any advice would be very much appreciated before I go bald from pulling my hair out..
Kind regards,
JS

 

[vcoolio]

Hello Sterjohn,

By default, Excel will ignore leading zeros when typed as a number. The simplest method I find is to convert the number to text. To do this, simply place an apostrophe(') in front of the mobile number. Your zeros will remain thereafter.

I hope that this helps.

Cheerio,
vcoolio.

 

[bebo021999]

With A1 is phone number, B1 is email
I guess A1 is custom format as 00000000000 (11 zeroes)

If you want to take 11-digit phone number then combination formula should be:

=RIGHT("0000"&A1,11)&B1

 

[sterjohn]

Hello Sterjohn,

By default, Excel will ignore leading zeros when typed as a number. The simplest method I find is to convert the number to text. To do this, simply place an apostrophe(') in front of the mobile number. Your zeros will remain thereafter.

I hope that this helps.

Cheerio,
vcoolio.

Hi vcoolio, thanks for your reply..
Unfortunately, I did try the apostrophe method as you suggest but somehow when I then upload the excel file to another platform, (used for sending bulk SMS messages to my customers) it
doesn't want to work.
It works without the apostrophe, (tried this with a single number and manual entry) so I need to find a way to keep the leading zero using another way.
Tried converting the column of phone numbers to text as well, still no joy.
Any further thoughts I could try?
Thanks again,
John.S

 

[sterjohn]

Hi vcoolio, thanks for your reply..
Unfortunately, I did try the apostrophe method as you suggest but somehow when I then upload the excel file to another platform, (used for sending bulk SMS messages to my customers) it
doesn't want to work.
It works without the apostrophe, (tried this with a single number and manual entry) so I need to find a way to keep the leading zero using another way.
Tried converting the column of phone numbers to text as well, still no joy.
Any further thoughts I could try?
Thanks again,
John.S

I have a long list of numbers to which I want to add "@e2s.smsbroadcast.com.au" result would be: 0457000605@e2s.smsbroadcast.com.au but it's trickier than I thought!

 

[Alex Blakenburg]

Did you try @bebo021999's suggestion ?
When you are in a cell with the mobile number in it, what do you see in the formula bar ie do you see the leading zero in the formula bar ?
What formula are you using to combine the 2 fields ? Are you by any chance using VBA ?

 

[vcoolio]

Hello John,

I've just done a little test on my suggestion in post #2 and all worked as expected. Did you save the workbook with Column A formatted as text before uploading it to the other platform?
Bebo's method should work as well but I didn't test it.
Another method which could work would be to format both Columns A and B as text and in Column C (still in general format) try this formula dragged down as far as needed:-

=CONCAT(RIGHT("0"&$A1,10),$B1)

Australian mobile numbers only have ten digits.

I hope that this helps.

Cheerio,
vcoolio.

 

[sterjohn]

Did you try @bebo021999's suggestion ?
When you are in a cell with the mobile number in it, what do you see in the formula bar ie do you see the leading zero in the formula bar ?
What formula are you using to combine the 2 fields ? Are you by any chance using VBA ?

Thanks for your reply Alex,
thankfully, finally, have it sorted using the Concat function.
All the best,
John.S

 

[sterjohn]

Hello John,

I've just done a little test on my suggestion in post #2 and all worked as expected. Did you save the workbook with Column A formatted as text before uploading it to the other platform?
Bebo's method should work as well but I didn't test it.
Another method which could work would be to format both Columns A and B as text and in Column C (still in general format) try this formula dragged down as far as needed:-

=CONCAT(RIGHT("0"&$A1,10),$B1)

Australian mobile numbers only have ten digits.

I hope that this helps.

Cheerio,
vcoolio.

Awesome!
Thanks vcoolio, the concat method solved the issue.
Really appreciate your help on this as it was driving me crazy..
All the best and thanks again.
John.S

 

[vcoolio]

You're welcome John. I'm glad to have been able to help and thanks for the feed back.

Cheerio,
vcoolio.