extract numberic value from cell and combine and add symbol in new cell

Hi all, i do have list of phone numbers key-ed in to excel sheet. Im removing the duplicates but excel dosent detect some of it example if the number has extra spaces or area code.

Example would be:
+6010-806 6666
010-8066666
010-806 6666

Different kinds of ways that it is saved. So meaning to say i would have to extract numbers out into another cell and add a "-" into it. If not when there are NUMBER starting 0 excel would mess it up. The "-" symbol must be from starting from left after the third digit. So makes the outcome 010-8066666.

The formula i have now:

Formula:

=SUMPRODUCT(MID(0&A2,LARGE(INDEX(ISNUMBER(--MID(A2,ROW($1:$25),1))* ROW($1:$25),0),ROW($1:$25))+1,1)*10^ROW($1:$25)/10)

Just need to add "-".

Originally Posted by TMS

This is pretty much a duplicate thread: https://www.excelforum.com/excel-gen...nt-format.html

Please don't waste people's time by starting new threads for the same issue. And it is common courtesy to reply to the answers you have in your original threads. Ideally, mark them as solved and link to the new thread if you feel that is helpful.

Please note that its not a duplicated thread, two thread clearly states different ways to get different results, one is extracting and one is removing duplicates.! I respect the forum and anyhow i wont waste forum space

That is not a large sample size to work on, but this formula in B2 will get all those numbers into the same format:

=SUBSTITUTE(SUBSTITUTE(A2,"+6","")," ","")

Copy down, as required.

Hope this helps.

Pete

Sorry, Trevor, I didn't see the other thread, and your post here had not appeared when I started my reply.

Pete

=TEXT(SUMPRODUCT(MID(0&A2,LARGE(INDEX(ISNUMBER(--MID(A2,ROW($1:$25),1))* ROW($1:$25),0),ROW($1:$25))+1,1)*10^ROW($1:$25)/10),"000\-0000000")