extract the landline/mobile numbers and Company names

[snjpverma]

I have a data as shown in below sample. I want to extract the landline/mobile numbers and the company name. How do I do it?
Note: Possible Pattern that I can observe is 10 to 11 digit contiguous numbers (sometimes with spaces in between). Regards to the company names: they are always on the first lines. Generally there is a two to three line gap after each new company as shown below.

Numbers highlighted in Red and Company names in Green for clear explanation.

Below is the data :
===============================================

National Electric & Hardware

1.7

(14) • Electrical supply store

Mumbai, Maharashtra

10 • 022 2570 5283

In-store shopping


Bhavna Ford, Sec 1, Navi Mumbai

1.0

(1) • Ford dealer

Navi Mumbai, Maharashtra

6 • 070459 52118



Ace Electricals

No reviews • Electrical substation

Navi Mumbai, Maharashtra

Open now



Supreme Maintenance Services

No reviews • Company

Navi Mumbai, Maharashtra

6 • 022 2779 5357



Siemens CRP Factory

4.4

(5) • Electric utility manufacturer

Navi Mumbai, Maharashtra

Open now • 086521 95208



Shiv Electricals

4.3

(133) • Electrical equi ent supplier

Ulhasnagar, Maharashtra

10 • 0251 270 6295



Adarsh Electrical & Hardware Store

3.0

(1) • Electrical supply store

Mumbai, Maharashtra

9:30



General Electric

2.7

(3) • Corporate office

Navi Mumbai

022 2769 7238



Eureka Forbes Office

No reviews • Electronics store

Open 24 hours • 086928 68340



Maharashtra State Electricity Board

1.2

(38) • Electric utility company

Navi Mumbai, Maharashtra

022 2763 4052Maharashtra State Electricity Board

1.2

(38) • Electric utility company

Navi Mumbai, Maharashtra

022 2763 4052

 

[GlennUK]

The Company Names are always on the first line ... of what? How are you supposed to detect what constitutes the "first line"?

 

[snjpverma]

The Company Names are always on the first line ... of what? How are you supposed to detect what constitutes the "first line"?

1st line after a 2 or more blank rows.
Below is the same data from Post # 1 when copied in an excel sheet.
Pattern: 1st line= company name, then company details with one blank row alternatively as shown below.
Please let me know if anymore clarifications required.

Data in Green is the company names and in Red Phone numbers.

 

[GlennUK]

You can generate markers like this, then use whatever ordinary Excel methods you like to extract how you want:

Book1 (version 3).xlsb
	A	B	C
1	National Electric & Hardware	Company
2
3	1.7
4
5	(14) • Electrical supply store
6
7	Mumbai, Maharashtra
8
9	10 • 022 2570 5283		Phone
10
11	In-store shopping
12
13
14	Bhavna Ford, Sec 1, Navi Mumbai	Company
15
16	1
17
18	(1) • Ford dealer
19
20	Navi Mumbai, Maharashtra
21
22	6 • 070459 52118		Phone
Sheet4

Cell Formulas
Range	Formula
B1:B22	B1	=IF(ROW()=1,"Company",IF(ROW()>3,IF(AND(OFFSET(A1,-1,0,1,1)="",OFFSET(A1,-2,0,1,1)="",A1<>""),"Company",""),""))
C1:C22	C1	=IF(SUM(IFERROR(VALUE(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))^0,0))>=8,"Phone","")
Press CTRL+SHIFT+ENTER to enter array formulas surrounded with curly braces.

 

[snjpverma]

It does work on the sample as intended.

However, I just noticed that the data I copied earlier shows blanks as stated earlier. Maybe, because I copied it to Msword, Notepad, etc and was trying different method to work it on my own.

And now when I copied the data from the original source, it shows like shown below. Could you please made the change in the formula for Company name to work for this dataset?

Note: Only change is there aren't two blank rows but just one blank row before each company name. And no blank rows after every row in between as stated in my original post.

Really sorry for this misfortune.

 

[jtakw]

Hi,

See if this works for you.
Leave First Row Blank (e.g. A1 row)
Formula in B2 copied down, then filter blank rows:

Book3.xlsx
	A	B
1
2	National Electric & Hardware	National Electric & Hardware
3	1.7
4	(14) • Electrical supply store
5	Mumbai, Maharashtra
6	10 • 022 2570 5283	02225705283
7	In-store shopping
8
9	Bhavna Ford, Sec 1, Navi Mumbai	Bhavna Ford, Sec 1, Navi Mumbai
10	1
11	(1) • Ford dealer
12	Navi Mumbai, Maharashtra
13	6 • 070459 52118	07045952118
14
15	Ace Electricals	Ace Electricals
16	No reviews • Electrical substation
17	Navi Mumbai, Maharashtra
18	Open now
19
20	Supreme Maintenance Services	Supreme Maintenance Services
21	No reviews • Company
22	Navi Mumbai, Maharashtra
23	6 • 022 2779 5357	02227795357
24
25	Siemens CRP Factory	Siemens CRP Factory
26	4.4
27	(5) • Electric utility manufacturer
28	Navi Mumbai, Maharashtra
29	Open now • 086521 95208	08652195208
30
31	Shiv Electricals	Shiv Electricals
32	4.3
33	(133) • Electrical equi ent supplier
34	Ulhasnagar, Maharashtra
35	10 • 0251 270 6295	02512706295
36
37	Adarsh Electrical & Hardware Store	Adarsh Electrical & Hardware Store
38	3
39	(1) • Electrical supply store
40	Mumbai, Maharashtra
41	9:30
42
43	General Electric	General Electric
44	2.7
45	(3) • Corporate office
46	Navi Mumbai
47	022 2769 7238	02227697238
48
49	Eureka Forbes Office	Eureka Forbes Office
50	No reviews • Electronics store
51	Open 24 hours • 086928 68340	08692868340
52
53	Maharashtra State Electricity Board	Maharashtra State Electricity Board
54	1.2
55	(38) • Electric utility company
56	Navi Mumbai, Maharashtra
57	022 2763 4052Maharashtra State Electricity Board	02227634052
58	1.2
59	(38) • Electric utility company
60	Navi Mumbai, Maharashtra
61	022 2763 4052	02227634052
Sheet990

Cell Formulas
Range	Formula
B2:B61	B2	=IF(A1="",A2,IFERROR(IF(IF(N(A2),"",MID(SUBSTITUTE(A2," ",""),IFERROR(FIND("•",SUBSTITUTE(A2," ",""))+1,1),11)+0),MID(SUBSTITUTE(A2," ",""),IFERROR(FIND("•",SUBSTITUTE(A2," ",""))+1,1),11),""),""))

 

[snjpverma]

@jtakw this worked exactly as intended. Thank you.

 

[jtakw]

You'e welcome, thanks for the feedback.