Delete Other Numbers Except Cell Numbers

I am new to use MS Office Excel 2010. I have 10 Lac Numbers but all numbers are mixed with each other like fax or land line numbers but i need only cell numbers except other numbers how can i do it.
cell numbers starts from "03". Like: "0300-1234567" but some cells have data like this (042-1234567 0300-1234567) How can i separate it.
All numbers are paste in A Column. How can i do it.
Please see attached file.

this is a very rudimentary approach. you would have to compare / test the inputs and outputs to confirm that it works for your purpose.

Dear icestationzbra

Yeah its working !!!!!
Thank you so much bro !

*The conventional definition of management is getting work done through people*
*but real management is developing people through work*

I face another problem in this sheet in A column many cell numbers typed like this:

0092-300-1234567
300-1234567
3001234567
+923001234567
923001234567
+92-300-1234567
+923001234567
042-1234567 *above conditions*
0421234567 *above conditions*
0092-421234567 *above conditions*
92421234567 *above conditions*
+92421234567 *above conditions*

how can i sort it without landline numbers from column A.

exactly what do you mean by the "another problem"?

how do those numbers need to be pruned?

what does "*above conditions*" mean?

provide expected results along with sample data.

*Above conditions* means:-

042-1234567 0092-300-1234567
042-1234567 300-1234567
042-1234567 3001234567
042-1234567 +92-300-1234567
042-1234567 +923001234567
042-1234567 923001234567
0421234567 0092-300-1234567
0421234567 300-1234567
0421234567 3001234567
0421234567 +92-300-1234567
0421234567 +923001234567
0421234567 923001234567
0421234567 923001234567
0092-4211234567 0092-300-1234567
0092-4211234567 300-1234567
0092-4211234567 3001234567
0092-4211234567 +92-300-1234567
0092-4211234567 +923001234567
0092-4211234567 923001234567
92421234567 0092-300-1234567
92421234567 300-1234567
92421234567 3001234567
92421234567 +92-300-1234567
92421234567 +923001234567
92421234567 923001234567
+92421234567 0092-300-1234567
+92421234567 300-1234567
+92421234567 3001234567
+92421234567 +92-300-1234567
+92421234567 +923001234567
+92421234567 923001234567
+92-42-1234567 0092-300-1234567
+92-42-1234567 300-1234567
+92-42-1234567 3001234567
+92-42-1234567 +92-300-1234567
+92-42-1234567 +923001234567
+92-42-1234567 923001234567
0092-421-1234567 0092-300-1234567
0092-421-1234567 300-1234567
0092-421-1234567 3001234567
0092-421-1234567 +92-300-1234567
0092-421-1234567 +923001234567
0092-421-1234567 923001234567
021-1234567 0092-345-1234567
051-1234567 323-1234567
021-31234567 3001234567
051-31234567 +92-300-1234567
021-31234567 +923001234567
042-1234567 923001234567

How can i extract cell numbers in these conditions
from column A without dash like (03001234567)

Try this in place of the formula icestation zebra gave you:

=IFERROR(TRIM(SUBSTITUTE(IF(ISNUMBER(FIND("03",A2)),
IF(FIND("03",A2)=1,MID(A2,1,LEN(A2)),
IF(ISNUMBER(FIND(" 03",A2)),MID(A2,FIND(" 03",A2),LEN(A2)-FIND(" 03",A2)),"Not cell")),"Not Cell"),"-","",1)),"Not Cell")

Dear Down Under

In this file each cell of column A have a cell number
i put your formula in column B cell but the result is
shown in attached file.

Oh, I thought you were referring to the original sheet.

In the example you just gave, which ones are the cell numbers and how do you want them to display? So for instance, what result should be displayed on the first line:

042-1234567 0092-300-1234567

Dear Bro

In our country Cell Numbers starts from 03 like 03001234567,
03121234567, 03451234567, 03151234567, 03231234567 &
03211234567 e.t.c.

But in some directories international code has been inserted like this
00923001234567 or 0092-300-1234567 so it is the problem now
and i have a mix directory which has mix cell numbers with land
line numbers and i need only cell numbers from column A but in
Column A i create a situation which i experiencing from my huge
data files in Column A. Land line and cell number typed in a one cell
with and without international code and dashes and 0 like attached
file so how can we get a cell number from column A.

I want this type of cell number from column A 03001234567.

Dear Down Under

Please check the attached file yeah your
formula is working but brother it's not
working on my attached real data file.

Dear Down Under
Its working on other new situation numbers but not working on past situation
which we discussed *=0&(SUBSTITUTE(MID(A1,FIND(3,A1,FIND(" ",A1,1)),20),"-",""))*
on past posted situations. Pls check attached file which cells red its mean it's
contain cell numbers but formula says not cell.

This is starting to get a bit beyond my abilities in excel I'm afraid. You can try this in cell B1:

=IF(ISNUMBER(FIND("03",A1)),IF(FIND("03",A1)=1,SUBSTITUTE(MID(A1,1,LEN(A1)),"-",""),IF(ISNUMBER(FIND(" 03",A1)),SUBSTITUTE(MID(A1,FIND(" 03",A1),LEN(A1)-FIND(" 03",A1)),"-",""),"Not cell")),IF(ISNUMBER(FIND("3",A1,FIND(" ",A1,1))),0&SUBSTITUTE(IF(ISNUMBER(FIND("3",A1,FIND(" ",A1,1))),MID(A1,FIND("3",A1,FIND(" ",A1,1)),LEN(A1)-FIND("3",A1,FIND(" ",A1,1))+1),A1),"-",""),"Not Cell"))

But if that doesn't work I'm afraid I can't really be of much help.

Not be so afraid brother you are best !!!! Thank you so much Brother !!! Awesome !!!

Dear Down Under

I want change in this formula or maybe i need some small change in your posted formula

but first i need change on this formula only

=0&(SUBSTITUTE(MID(A1,FIND(3,A1,FIND(" ",A1,1)),20),"-",""))

this formula work on this type of number: 042-1234567 0092-300-1234567
but this formula is not working on this condition:- 0092-300-1234567

That type of example was never seen in the original workbook.

To be honest, it's getting to the stage now where there are so many different ways of your data presenting that you need either a Macro or some sort of multiple substitute UDF, neither of which I have any knowledge of. I'm afraid you are probably going to need someone else to efficiently solve your problem.

Yeah Brother you are right in my workbook it's not present. May be when i feel problem relates these formulas i post new thread for each formula. because the conditions are changes so each problem solved by each thread ok brother.

You just need a more efficiently solution really. All these nested substitute and if functions I'm putting on get really messy after a while. I think some sort of macro solution would be most efficient.

Dear Down Under
My Friend
I really appreciate your efforts for my problem. Thanks for your advice bro!

{=IF(OR(ISNUMBER(FIND({"03"," 03"," 300","-300","0092-300","0092300","0092 300","92300","+92300","+92-300","+92 300"},A1))),IF(LEN(A1)<10,"Not Cell",IF(OR(MID(A1,LEN(A1)-7,1)={"-"," ",""}),MID(A1,LEN(A1)-10,3)&RIGHT(A1,7),RIGHT(A1,10))),"Not Cell")}
Try this

In your formula i put all local mobile companies
codes and i apply on my sheet then the result is attached.
The first 0 of mobile numbers are not showing in result and
other numbers are also mixed with mobile numbers.

Our local mobile codes are as under:

0300, 0301,0302,0303,0304,0305,0306,0307,0308,0309
0311, 0312, 0313, 0314, 0315, 0316
0321,0322,0323,0324,0325
0331,0332,0333,0334,0335, 0336
0341,0342,0343,0344,0345,0346,0347

Please see the whole result.

Now Formula as under:

=IF(OR(ISNUMBER(FIND({"03"," 03"," 300","-300","0092-300","0092300","0092 300","92300","+92300","+92-300","+92 300"," 301","-301","0092-301","0092301","0092 301","92301","+92301","+92-301","+92 301"," 302","-302","0092-302","0092302","0092 302","92302","+92302","+92-302","+92 302"," 303","-303","0092-303","0092303","0092 303","92303","+92303","+92-303","+92 303"," 304","-304","0092-304","0092304","0092 304","92304","+92304","+92-304","+92 304"," 305","-305","0092-305","0092305","0092 305","92305","+92305","+92-305","+92 305"," 306","-306","0092-306","0092306","0092 306","92306","+92306","+92-306","+92 306"," 307","-307","0092-307","0092307","0092 307","92307","+92307","+92-307","+92 307"," 308","-308","0092-308","0092308","0092 308","92308","+92308","+92-308","+92 308"," 309","-309","0092-309","0092309","0092 309","92309","+92309","+92-309","+92 309"," 341","-341","0092-341","0092341","0092 341","92341","+92341","+92-341","+92 341"," 342","-342","0092-342","0092342","0092 342","92342","+92342","+92-342","+92 342"," 343","-343","0092-343","0092343","0092 343","92343","+92343","+92-343","+92 343"," 344","-344","0092-344","0092344","0092 344","92344","+92344","+92-344","+92 344"," 345","-345","0092-345","0092345","0092 345","92345","+92345","+92-345","+92 345"," 346","-346","0092-346","0092346","0092 346","92346","+92346","+92-346","+92 346"," 347","-347","0092-347","0092347","0092 347","92347","+92347","+92-347","+92 347"," 331","-331","0092-331","0092331","0092 331","92331","+92331","+92-331","+92 331"," 332","-332","0092-332","0092332","0092 332","92332","+92332","+92-332","+92 332"," 333","-333","0092-333","0092333","0092 333","92333","+92333","+92-333","+92 333"," 334","-334","0092-334","0092334","0092 334","92334","+92334","+92-334","+92 334"," 335","-335","0092-335","0092335","0092 335","92335","+92335","+92-335","+92 335"," 336","-336","0092-336","0092336","0092 336","92336","+92336","+92-336","+92 336"," 311","-311","0092-311","0092311","0092 311","92311","+92311","+92-311","+92 311"," 312","-312","0092-312","0092312","0092 312","92312","+92312","+92-312","+92 312"," 313","-313","0092-313","0092313","0092 313","92313","+92313","+92-313","+92 313"," 314","-314","0092-314","0092314","0092 314","92314","+92314","+92-314","+92 314"," 315","-315","0092-315","0092315","0092 315","92315","+92315","+92-315","+92 315"," 321","-321","0092-321","0092321","0092 321","92321","+92321","+92-321","+92 321"," 322","-322","0092-322","0092322","0092 322","92322","+92322","+92-322","+92 322"," 323","-323","0092-323","0092323","0092 323","92323","+92323","+92-323","+92 323"," 324","-324","0092-324","0092324","0092 324","92324","+92324","+92-324","+92 324"," 325","-325","0092-325","0092325","0092 325","92325","+92325","+92-325","+92 325"," 316","-316","0092-316","0092316","0092 316","92316","+92316","+92-316","+92 316"},A2))),IF(LEN(A2)<10,"",IF(OR(MID(A2,LEN(A2)-7,1)={"-"," ",""}),MID(A2,LEN(A2)-10,3)&RIGHT(A2,7),RIGHT(A2,10))),"")

for a moment there while looking at the formula, i thought a portion of my monitor screen had pixelated very badly...

what a formula! it looks like you have entered all of your phone numbers right into the formula! does the formula output anything at all???

Yeah brother you right but what can i do
its working but with some errors. I put
all local mobile companies codes in your formula
and i apply on my sheet then the result is attached.
The first 0 of mobile numbers are not showing in result and
other numbers are also mixed with mobile numbers.

Our local mobile codes are as under:

0300, 0301,0302,0303,0304,0305,0306,0307,0308,0309
0311, 0312, 0313, 0314, 0315, 0316
0321,0322,0323,0324,0325
0331,0332,0333,0334,0335, 0336
0341,0342,0343,0344,0345,0346,0347

Please see the whole result in attached file.

Jindon

This is obviously using some regular expression call. Can you pls explain the vbscript.

Please Login or Register  to view this content.

Since OP clearly described the details for cell phone pattern, I've just code it in the pattern.

Let's just forget about the open bracket after 0? at the moment

"0?3" = start with 0 or without 0 and followed by 3, ? represents 1 or none of character before it.
those special characters are called "Meta characters", e.g [ ] - \ * ? n ^ $ ( ) | + , holding special meaning.

(0[0-9]|1[1-6]|2[1-5]|3[1-6]|4[1-7])
0 with 0 to 9 or
1 with 1 to 6 or
2 with 1 to 5 or
3 with 1 to 6 or
4 with 1 to 4
Pipe represnets "or" and brackets make a group, so 0?3 floowed by either one of such combination as OP described.

-?\d{7} = with or without hyphen followed by 7 digits of any numbers
\d represents numbers and \D is oppsite(non numeric character)
{7} represents 7 character of before it.
You can also make it loike {6,7}, means 6 to 7 characters, or {6,} means more than 6 characters

So that the pattern satisfied all the possible combination that OP described.

Then you can refer the position of retrieved string using Brackets using "submatches" property.
If you look at the pattern and the first open bracket is closing at the end.
That means submatches(0), index for submatches begins with 0, refers without first "0", because we only need that part of the string and we can add "0" to it afterwards.
Otherwise, we will have problem if the string has "0".

This is rough explanation and you can google about the keywords like
VBA Regualr Expressions Meta Character
etc.

Dear jindon
where i post your function i am new to use MS Excel 2010 Professional plus.
Can you give me some sample to apply this function on my data file.

Here's a file with the User Defined Function (UDF)

!!!!!!! Marvelous !!!!!!! You solved my problem brother !!! Your function not shows any error on my big data file Awesome !! Down Under is also right he advice me about these functions and programming awesome !! Thanks to you all who helps me even who try to help me love you all !!!!

If 2 numbers in one cell like as under
0300-8255746-0321-241004

0321-241004

Should hit ?

It doesn't follow the digits to me.

Can you clearly define the pattern?

Brother your function is awesome but in directories sometime important numbers are typed mistakenly like after mobile code 6 or five digits.

in function
.Pattern = "0?(3(0[0-9]|1[1-6]|2[1-5]|3[1-6]|4[1-7])-?\d{7})"
in d{7} i wants 7 to 5 digits allowance
Is it possible ?

please check attached file my both problems are shows in attached file.

Thanks brother
Little bit change i need in the below condition
Original : 0345-8255746-0321-2541004
Result : 03458255746, 03212541004

Needs : 03458255746
03212541004
I need in line result because of my software requirement.

I think it is functioning to me.

I you are talking about those to be hit
3257543
3257542
3254199
then you need to change {5,7} to {4,7}

Needs : 03458255746
03212541004
I need in line result because of my software requirement.

Please Login or Register  to view this content.

Originally Posted by jindon

Please Login or Register  to view this content.

This function shows result like as under in excel

0300825574603212541004

You need to set the cell format as wrap text.

Thank you so much !!!