Nested Functions

[linaeum66]

I am trying to make an equation that reformats international phone numbers.
For instance, if this is the input: +44(0)1111111111, I would like to make an equation to remove the "+", remove the "(0)" and add "011" as a prefix to the number.

For example +44(0)1111111111 would become 011441111111111

I have made the following formula to remove the "+", remove the "(0)"

=SUBSTITUTE(SUBSTITUTE(A1,"(0)", ""),"+","")

to yield: 441111111111

but how would I make a nested concatenation to add the "011" prefix in a single function?

 

[dreid1011]

=SUBSTITUTE(SUBSTITUTE(A1,"(0)", ""),"+","")

Perhaps this:

="011"&SUBSTITUTE(SUBSTITUTE(A1,"(0)", ""),"+","")

 

[jtakw]

Hi,

My understanding is that "prefix" within the brackets, in your sample (0) might sometimes be more than a single digit, so you might want to use something like this instead:

Book1
	A	B
1	+44(0)1111111111	011441111111111
2	+44(044)1111111111	011441111111111
3	+441111111111	011441111111111
Sheet83

Cell Formulas
Range	Formula
B1	="011"&SUBSTITUTE(IFERROR(LEFT(A1,FIND("(",A1)-1)&MID(A1,FIND(")",A1)+1,15),A1),"+","")

Edit: updated formula to include possible numbers Without prefix in brackets.