Home

Excel Formatting

DM_Britams
New Contributor
Hi guys,
I have used custom formatting to add the country code to a number cell. So for eg. Original was 6123456789 and I have customised using 0031000000000 to get result 00316123456789. However, I now want the cell value to be 00316123456789 and not keep the old value. This makes it easier when uploading telephone contact lists. At the moment, when I do this, it changes the value back to the original.

Do you know how I can convert the cell value permanently?

Many thanks for your time and input!
Gemma
1 Reply

I do not think this is possible with cell formatting as this option only deals how your value is being shown not the value it self. However you can add a helper column and create a formula like this (assuming your numbers are located in column A):

="0031" &A1

to convert your number to a string starting with 0031. If you need this to be a number then you can try below formula:

=("31&A1)*1

however this time you need to change cell formatting to show this number as desired.

Related Conversations
Conditional Formatting - Consider Only Value as Lowest Value
Ben Smith in Excel on
2 Replies
Date Formatting Won't Change
Steve Gould in Excel on
10 Replies
Format Date in header and footer
Joe Cangelosi in Excel on
3 Replies
Conditional Formatting values across tables
ChrisJP in Excel on
5 Replies
Adding Display Zero as Dashes to Custom Excel Number Format
emmagreen in Excel on
1 Replies
Relative conditional formatting
melissach in Excel on
8 Replies