Extract Mobile Numbers from data

R

RIZVI

Active Member
Joined
Jan 1, 2011
Messages
283
Hi,

I have a huge data sheet and under column titled Cust. Contact details: Postal address of customer along with his / her email IDs, (some times more than 2 or 3) and also their mobile number and landline number are given.

1. I need to extract ONLY the mobile numbers in the cells adjacent to this column.
2. I need to collate all these mobile numbers obtained in this column of point 1 above into one cell.


Looking forward to help.


Rgds,

Rizvi
 

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.
Sort by date Sort by votes
In Column A there are customer names like A2: M/s ABC Enterprises, A3: XYZ Solutions, A4: Mr.NhI, A5: kiuo and so on till A2000

In Column B: There are the contact details given against each customer like for M/s ABC Enterprises, in B2 will be: Robert De'Mello, 34 Street, 1st lane, Delhi, India - 100625, email: sdfg@gmail.com, Mob: +919420688987, tel: +91 0832 25689455, MrSushant Bhandari, yhuuyt@siy.com, 98564985478, Prashant Agrawal +910802569158, 9888554658

And so on for each customer.

In Cell C2 i need only the mobile numbers like in above case the data required is: +919420688987, 98564985478, 9888554658


Hope it is clear now. Still some more information is required then please let me know.

RGDS,

Rizvi.M.H.
 
Upvote 0
n Column A there are customer names like A2: M/s ABC Enterprises, A3: XYZ Solutions, A4: Mr.NhI, A5: kiuo and so on till A2000

In Column B: There are the contact details given against each customer like for M/s ABC Enterprises, in B2 will be: Robert De'Mello, 34 Street, 1st lane, Delhi, India - 100625, email: sdfg@gmail.com, Mob: +919420688987, tel: +91 0832 25689455, MrSushant Bhandari, yhuuyt@siy.com, 98564985478, Prashant Agrawal +910802569158, 9888554658

And so on for each customer.

In Cell C2 i need only the mobile numbers like in above case the data required is: +919420688987, 98564985478, 9888554658


Hope it is clear now. Still some more information is required then please let me know.

RGDS,

Rizvi.M.H.
 
Upvote 0
How can you recognize the mobile numbers in the string containing other numbers (rules of extraction which can be "understood" by excel).
 
Upvote 0
István Hirsch said:
How can you recognize the mobile numbers in the string containing other numbers (rules of extraction which can be "understood" by excel).
Click to expand...

They are always 10 digit number. Sometimes they may hv prefix like 0 or+91 or only 91
 
Last edited:
Upvote 0
There's no pattern here that I can see. How would we be able to differentiate mobile numbers from regular phone numbers? Now, if each mobile number was preceded by "Mob:", that would be a simple matter.

Regards,

CJ
 
Upvote 0
Yes, Presently there is no pattern. But I can add word Mob in front of each mobile number, though it is going to be gigantic task in itself.
 
Upvote 0
RIZVI said:
Yes, Presently there is no pattern. But I can add word Mob in front of each mobile number, though it is going to be gigantic task in itself.
Click to expand...

The task would be gigantic but still simpler than manually extracting the numbers. So, let's say you went thru each cell in column B and added the characters "Mob " (note the space) directly in front of the number to be extracted (replace any '+') so that the data would look like this:

Robert De'Mello, 34 Street, 1st lane, Delhi, India - 100625, email: sdfg@gmail.com, Mob 919420688987, tel: +91 0832 25689455

Then, in C2 use the formula

=MID(B2,SEARCH("Mob",B2)+4,12)

Regards,

CJ
 
Upvote 0
Another option that would be a lot less work, but isn't exactly what you were looking for, is to use comma-deliminated text-to-columns. This will separate out all data between commas and then you can manually find the mobile numbers (although they may not end up in the same column in every row.)

CJ
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

B
  • Question
VBA Extract data from one Workbook and Transpose Paste to another
Replies
5
Views
911
DanteAmor
DanteAmor
M
  • Question
VBA Macro to Extract Outlook Attachment Excel Data and Paste into Specific Excel Worksheet Range
Replies
0
Views
596
mclawler
M
T
  • Question
Extracting information from multiple columns located in different workbooks to a specific worksheet in another workbook
Replies
0
Views
542
Tyjyt
T
M
  • Question
Extracting and Counting/Averaging Data from a Range
Replies
1
Views
542
Skybluekid
Skybluekid
A
  • Question
Calculating No. of (unique) Transactions, data of 1st appearance and last date from a running Ledger
Replies
0
Views
191
ateeqsindhu
A

Forum statistics

Threads
1,215,575
Messages
6,125,628
Members
449,241
Latest member
NoniJ

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top