Converting a number into particular format

Harry_1234

New Member
Joined
Aug 19, 2017
Messages
31
Hello,

What is the best way to convert following format into e.164 in excel i.e. i would like to convert numbers in the format 123 456-0001 to \+11234560001.
Column A Column B

123 456-0001 \+11234560001
123 456-0005 \+11234560005
123 456-1000 \+11234561000
 

Some videos you may like

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)

kennypete

Board Regular
Joined
Apr 19, 2008
Messages
227
Office Version
365, 2019
Platform
Windows
The B1 formula here should do the trick:
Book2
AB
1123 456-0001
\+11234560001
Sheet1
Cell Formulas
RangeFormula
B1B1=INT(SUBSTITUTE(SUBSTITUTE(A1,"-","")," ",""))

You can either prepend the "\+1" or number format adding "\+1" (what I did here).
Whether this is the "best" way probably depends on factors that are not outlined here - e.g. I have presumed that the 123 456-0001 is text and not a representation presented by a formula, etc.
 

Harry_1234

New Member
Joined
Aug 19, 2017
Messages
31
The B1 formula here should do the trick:
Book2
AB
1123 456-0001
\+11234560001
Sheet1
Cell Formulas
RangeFormula
B1B1=INT(SUBSTITUTE(SUBSTITUTE(A1,"-","")," ",""))

You can either prepend the "\+1" or number format adding "\+1" (what I did here).
Whether this is the "best" way probably depends on factors that are not outlined here - e.g. I have presumed that the 123 456-0001 is text and not a representation presented by a formula, etc.
Thank you!! This was really helpful.
 

Watch MrExcel Video

Forum statistics

Threads
1,099,055
Messages
5,466,305
Members
406,474
Latest member
osama beskales

This Week's Hot Topics

Top