Solved Clarification to separate phn number and mobile number in xl

June 26, 2012 at 22:52:09
Specs: Windows 7
Mobile No
8686030909
9392903671
9866797302
8885000806, 8885000805
9866628169
9849085678
9849019644
9059640068
9290025162
8897373999, 9966070809
9440508991
9494862015, 9032792858
9849420511
9346934700
9866611995
9440808171
9885688350
9666777799
9849790051
8179744472
9985000494
7893789260
9948668424, 9505367484
9246889986
9985646037, 9885146505
9866233328, 9032027688
9959559425, 9849182544
9949628294
9989355585
9849023363
9000517886
9394777975, 9291549590
9985111542
9703389101
9849093117, 9391793117
9347646430, 9348446431
9030984689, 9948328013
9985380674
9885365000
9581999595
9849051412
9247269421
9866686544
8801000045
9885243979
9177900625, 9177900626
9908550077
9642499000
9866779962
9912661616
9052783362
9000016640
9849204060
9000092999
9989759990
8121191914, 8121191915
9000077688
9989898822
9989024958
9985222236
9951754245
9246192384
9849000518
9849502323
8886806769, 9000569234
9866929669
9701771122
9849877388
8008258888
9391959667
9701808260
8297200099
9849012862, 9989252859
9849012862, 9989252859
9391195511
9246878478
9030638701
9246533508
9391604040
9849185263
9642427933
9849124445
9676664222
7893800139
9000000579, 9000000059
9866801015
9701695678, 9849912345
9246889986
9848276781
9391311568
8096312867
9246271899
9160879999
9000806685
9247117837
9247583975, 9491327244
9014332444
9848810696
9848035911
9642108108
9848516802, 7893970502
9603976363
9985787217
9849745871
9392374980
9989185412
9573155172, 9492849845
9912627601
9347337581
9948469665
9030511159
9866232228, 9700062856
9849154555
9246528519
9390096295, 7842248455
9849196541, 9618232320
9247278290
9949133733
9849019450
9949570888, 9866882224
9348777779
9391000015
9959245747
9441881515
9866304050
9014228880, 9963628880
9502922666
9849021470
9848579790, 9912191565
9533565609, 9177999609
9000544440
9177227598
9866066980
9000226210
9885496756, 9949158391
9885573772
9849179369, 8019355179
9347226735
7207845627
9912311234
9849161272, 9346161272
9989590111
9951455190
9000151551
9490939081, 9177839155
8790327356
9633489257, 9633961921
9676795697, 7569651303
9160019188
9995805415
9963644999, 9949793999
9866112887
9959189100, 9550603190
9666833302
9052899991
9959938767
9848223451
9959984241
9746043196
9849064437
9947006607
9971381467
8547500100
9666655568
9246242498
9705555570
8885176089
9866072411
9848880884
9848850450
9849384783
9000777208
9866668844
7702860393
9989478993, 9246578993
9394242850
9959629772
8978278330
9397677889
9704811148
9949446681, 9347728549
9849220478
9849645505, 9849645510
9248001601
9848121401
8142549229, 9346415949
9849906166
9849001212
9652747576, 9866317377
9848132627
9494480571
9966510999, 9989199954
66591441
9995224446
9094788996
9840034329
9444471367
9840234207
9840077009
9962222962
9841182116
9840358686
9840363949
9841563783
9941191310
9840199934
9940306543
9841763654
66421847
9841015442
9841010631
66590931
9790962224
9790962224
9790962224
9790962224
9841662299
43576562
24335727
9940474147
9840367491
9841654674
9894910772
9363007960
9840312823
9884215357
9840261026
9940255560
9710407143
9841610281
9841866168
9840115559
9940485299
9840103503
9444012442
28345831
9176698101/42060137
24802553
9840700700
9789939254
9003217857
9444133343
9962547555
25361150
9841112005
9176548473
9841327387
9840046821
9381081520
9940488992
28414485/9444242924
24950886
9710665312
45508000
9340098400
9884248131
66320631
9789907899/65554222
9380491697
9941650445
9840684255
9384620627
9380458125
9962718072
30897200
9710931426
9884629176
9840024081
9884504514
9710999100
9840438692
9840326020
9894018682
9940514311
9444124339
9840340565
9327921305
9884426430
9380770540
9941412626
9382744745
22670006
9789779834
9884662862
9500031667
9789062915
9940377700
9840892939
28142901
8825013794
9962422009
9841677985
9840219228
9841366559
9840454347
26269947
66325283
9941636000
9042504669
9840535336
9841709111
25387320
9444217701
9382341249
9444127240
23454091
9841934578
9840203191
9962112232
9952936746
9600002131
9941880278
9841387858
9884188160
9600471775
9600119350
9841719112
9500150901
66590401
23723282
24711001
9840066970
9840325656
43036387
9940648085
9677086555
9841096867
9940588589
9962107168
9841483123
9841918705
9840603056
9176777455
9841288631
66320843
9884095155
9840144484
9841779699
9941456867
9840663040
9840125848
9841314573
9840869808
9840041425/ 25386166
8754576156
9840093402
9677268580
9840888088
9940396005
9566211166
9344848686
9840025450
9841384949
66421514
9003254555
9884467760
9840690000
9894228386
66425227
9444694388
9444057080
9962811567
9551177952
9840923702
66421447
66591409
971012318
9382895657
9840017484
9840548577
9941266602
9710999100
9841206230
9962813917
23622890
9840800800
22252927
8056010889
9865749083
9962959633
9003197874
8925517747
8015637559
9944111303
9840244028
9790907880
9884042707
9840296408
9566100636
9840039215
9940160585
9500150901
66590952
9841327308
9840690000
45036222
9841077730
25581950
9003021174
9444408615
9884373729 Praveen
9094386058
9884832812
9940142349
9841074842
9941325767
9884443064
66321701
66423781
42859434
9382877815
9840389489
9841724154
9500196666
9952044253
9840144484
9884619550
9952942078
9940142349
9884367772
9865749083
9841006131
9884636206
66321354
9500058853
9841880273
9884182210
9840204293
42647271
42641111
8015775712 / 9962850633
66329271
8939814999
8807681976
24994837
9841251059
9884257007
28265897
9840054928
9884454689
9629400315
9841113151
9444700351
9841029921
9841052002
9840370846
9840026379
28152913
9840279793
26571542
9841049350
9500074076
9444712271
9840721742
7845669055
9444902744
9080715210
9884495128
9962015786
9841700022
9381137345
9841051460
9790872744
9841118959
9841441288
9884564800
9884884428
66077879 / 98407 32808
9840025171
9382886381
9940100645
9843052580
9382233311
9600036641
9840400534
24813558
9840448157
9444172817
9840869898
9940300921
9841454165
9789807818
9843028089
9884528800
9940333902
9841193929
9840101362 / 9840189967
9940340599
9840254832
9841880273
9841073289
9841072663
9444256826/24424298
9841125926
9962859468
9790713822
9283149938
9840099585
9380425506
9444694388
i have a data like this. how do i separate mobile number and landline number in different columns

See More: Clarification to separate phn number and mobile number in xl

Report •


✔ Best Answer
June 28, 2012 at 06:27:35
The problem is that your data is not consistant. If you had a column that contained either a 10 digit number or an 8 digit number, the process could be automated fairly easily.

However, since you have "random" formats of data like these, there is going to have to be some manual intervention:

9985646037, 9885146505
28414485/9444242924
9884373729 Praveen

Here's the best I can offer:

With your list of data starting in A1...

Enter this in B1:

=IF(LEN(A1)>8,A1,"")

Enter this in C1:

=IF(LEN(A1)=8,A1,"")

Select B1 and C1 and drag these formulas down to the bottom of your list.

In Column B you should have:

1 - All of the 10 digit numbers
2 - All of the random formats mentioned above
3 - A bunch of blank cells

In Column C you should have:
1 - All of the 8 digit numbers
2 - A bunch of blank cells

Next, select Columns B and C and do an Edit...Copy...Edit...Paste Special - Values

This will eliminate the formulas and leave just the numbers.

Next, Select Column B and Sort Ascending (only sort on Column B)
Next, Select Column C and Sort Ascending (only sort on Column C)

This should bring all of your numbers to the top of the columns.

Now, way at the bottom of Column B you should have all of your random formats.

Use Data...Text To Columns to separate these into separate Columns.

Now repeat the above steps on that data and move stuff around until you have what you are looking for.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.



#1
June 27, 2012 at 04:48:32
A posting tip...

The next time you want to post example data, please condense it down to a few examples of the various types of data you have. Just enough so that we can see one or two of each format. No one wants to scroll through hundreds of lines to get to your question.

Once you've posted the "input" data, post the corresponding desired output. I'm not sure what you looking for as a result.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#2
June 27, 2012 at 05:01:05
I am extremely sorry for the trouble. Will take your advice.

My requirement is if i have a data like

66591441
9995224446
9094788996
9840034329
how do I separate the land line number (66591441) to a separate column.
Column A Column B
9995224446 66591441
9094788996
9840034329


Report •

#3
June 27, 2012 at 06:25:58
Now I am totally confused.

Your long list had data that showed many lines of numbers separated by commas, slashes, spaces and slashes with spaces.

You short list shows none of that.

Your long list shows instances where there is more than one 8 digit number (landline?) group together, like this:

9840454347
26269947
66325283
9941636000

Your short list doesn't show anything like that.

I'm really not sure what you are trying to do.

Perhaps you need to post a little bit more example data - not hundreds of lines, but enough so that we see the different permutations of your data - along with some words that explain in a little more detail haht you are trying to accomplish.

Keep in mind that we can't read minds, so you need to explain your requirements in enough detail so that we can understand it. All we see is a bunch of numbers and have no idea what's a landline and what isn't.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

Related Solutions

#4
June 27, 2012 at 22:08:25
Hi Sir,

Whatever is the number of examples, my requriement is "66591441"- is a phone number and 10 digit numbers are mentioned as "mobile number", so, i need to arrange all 10 digits in one column and 8 digits in other column. So, the result which i want is, in column A - all mobile number (10 digit) and in column B all phone numbers (8 digits)

assuming this is the example:

9840454347
26269947
66325283
9941636000
Under column A
9840454347
9941636000

Under column B
26269947
66325283


Report •

#5
June 28, 2012 at 06:27:35
✔ Best Answer
The problem is that your data is not consistant. If you had a column that contained either a 10 digit number or an 8 digit number, the process could be automated fairly easily.

However, since you have "random" formats of data like these, there is going to have to be some manual intervention:

9985646037, 9885146505
28414485/9444242924
9884373729 Praveen

Here's the best I can offer:

With your list of data starting in A1...

Enter this in B1:

=IF(LEN(A1)>8,A1,"")

Enter this in C1:

=IF(LEN(A1)=8,A1,"")

Select B1 and C1 and drag these formulas down to the bottom of your list.

In Column B you should have:

1 - All of the 10 digit numbers
2 - All of the random formats mentioned above
3 - A bunch of blank cells

In Column C you should have:
1 - All of the 8 digit numbers
2 - A bunch of blank cells

Next, select Columns B and C and do an Edit...Copy...Edit...Paste Special - Values

This will eliminate the formulas and leave just the numbers.

Next, Select Column B and Sort Ascending (only sort on Column B)
Next, Select Column C and Sort Ascending (only sort on Column C)

This should bring all of your numbers to the top of the columns.

Now, way at the bottom of Column B you should have all of your random formats.

Use Data...Text To Columns to separate these into separate Columns.

Now repeat the above steps on that data and move stuff around until you have what you are looking for.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#6
June 28, 2012 at 22:48:49
Fantastic Sir. Nice to meet a excel genius like you. One more doubt please.
my data is like this.
#17-86/1,Road No. 1, Kamalapuri Nagar Chaithanyapuri, Hyderabad -500060
Madhapur, Hyderabad -500081
#6-3-650, Shop #G-4,Maheshwari Chambers , Somajiguda, Hyderabad -500082
Plot No. 6, Room No. 101Madhapur, Hyderabad -500081
#404,Park View Apartments, Adikmet , Vidya Nagar, Hyderabad -500044
Maruthi Nagar, Santosh Nagar Santhosh Nagar, Hyderabad -500079
201, Sneha Residency Sneha Residency, Shalivahanagar Moosaram Bagh, Hyderabad -500036
Now , i want to separate location and pincode to next next columns separately. Please assist Sir.

Report •

#7
June 29, 2012 at 05:58:45
First, since this a totally separate question, please post it in its own thread.

Second, you know your data, we don't. We don't know what a "pincode" is.

As I said earlier in this thread, you need to be detailed in your explanation and examples (both input and output) so that we can understand your requirements. If we try and "guess" what you want your output to look like, we usually end up just wasting time working on the wrong problem.

Third, please click on the following line. That is a link to instructions on how to properly post example data in this forum. The more you can make the data look like it does in your spreadsheet, the better we are able understand exactly what we are working with. Copying and pasting directly from a workbook is usually better than manually typing the data into a post.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •


Ask Question