I want to take clear phone numbers in excel

Question

I have phone numbers; I need to get rid of the extra characters and leave only numbers.

idk i tried all, checked video and other.

sorry if my question stupid

I never used formulas in excel sheets.

Answer 1

Try using REDUCE( ) function to substitute multiple values at once.

---

• Formula used in cell B1

=REDUCE(A1:A10,{"[","'","(",")","]"," "},LAMBDA(x,y,SUBSTITUTE(x,y,)))

---

The solution is based on the question and answer asked here: Solution provided by JvdV Sir. Reference link to post: Complex Substitute Beyond 64 Nesting Limit

---

Another alternative using TEXTSPLIT( )

---

• Formula used in cell B1

=MAP(A1:A10,LAMBDA(x,CONCAT(TEXTSPLIT(x,{"[","'","(",")","]"," "},,1))))

---

Answer 2

You could use:

Formula in B1:

=MAP(A1:A2,LAMBDA(s,CONCAT(TOCOL(--MID(s,ROW(1:17),1),3))))

Answer 3

The easiest option is to use Ctrl+H and replace (selection cells only) the unnecessary characters with blank space.

If you want a function, you can use SUBSTITUTE function and use that multiple times to remove the characters you want replaced

=SUBSTITUTE(SUBSTITUTE(A1,"(",""),"[","")

Answer 4

=VALUE(  TEXTJOIN("",TRUE,  IFERROR(  VALUE(  MID(A1,SEQUENCE(LEN(A1)),1)  ),  "")  )  )

The MID tears apart the input string into single characters. VALUE attempts to change each character into a number. Non-numerals will result in an error, so IFERROR changes those out for a blank ("").

TEXTJOIN then puts all the parts back together. It does not use a delimiter so that there is just a string with nothing new. It ignores all the blanks that IFERROR created.

Trick is, you now have a string and that won't be amenable to using a custom number format on to display them in some desired way. But... if the string starts with a 0, you will lose it (or them) if you convert the text result into a number.

But that can be overcome, so one more VALUE makes it a number.

The reason you can overcome it is that, unless you have numbers that need a variety of formats and that differ in length, you can just use "0" in the format string as many times as you need numerals. So a "0000-0000-00" string would give "0665-8888-01" fr display, even though the actual leading 0 was lost.