Please help me rearrange a single column (A) of data to four columns (B,C,D,E)
Column B will have Primary Category
Column C will have Establishment Names
Column D will have Addresses
Column E will have Telephone Numbers
each set of data in column A is separated by at least one blank row.
each data set begins with establishment name (ALL CAPS)
establishment name (ALL CAPS) may be 1 or 2 rows.
if it contains two rows of establishment name:
if ESTNAME1 is exactly found in ESTNAME2, only ESTNAME2 is used.
if ESTNAME2 is exactly found in ESTNAME1, only ESTNAME1 is used.
else, they will be concatenated. "ESTNAME1 ESTNAME2"
address(NOT all caps) is next to the establishment name and may also be 1 or 2 rows.
if it contains two rows of address, they will be concatenated. "ADDRESS1, ADDRESS2"
next to address is telephone number. always 1 line
telephone number will always contain at least 6 numbers on its right.
next to telephone number is the primary category
"Primary category: " always appears on primary category and only the words next to it is needed. Say, "Hotel & Restaurant" only for "Primary category: Hotel & Restaurant"
sometimes, an extra line follows after primary category. This always begins with "Inquire". This is useless.
the output will be four column of data with number of rows equal to the number of data sets on column A.
as long as the four column data is already correct, column A may be removed for its not needed anymore.
Data in column A looks like:
ACE PHILX HOTEL CORPORATION
Mando Pavilion Hotel United Nations
1000 Mando
522291121
Primary Category: Hotels
Inquire by email ·
AMORA TERRA HOTEL
Iznart Moxo City, Moxo
036568989
Primary Category: Hotels
HOTELLACAVANA
BEST WESTERN HOTEL LA CAVANA
G/F Hotel La Cavana 1234 Marc Pila Street
5000 Monague
5242631
Primary Category: Hotels
Inquire by email · Inquire by fax ·
BETELON GUEST HOUSE
Betelon Guest House Rova Madano
6450 Magabeck, Ore Nempho
(045)6690000
Primary Category: Hotels
Inquire by email ·
BINDO SUITES
BINDO SUITES
Pong Street Corner Pla Street Bindo 4000 Mora
5686894
Primary Category: Hotels
Inquire by email · Inquire by fax · Visit web site ·
CARPIT TOURIST
CARPIT TOURIST INN INC
Pres Meva Blvd Goto City, Bamu
(046)2656969
Primary Category: Hotels
------------------------
Column B: Primary Categories will be:
Hotels
Hotels
Hotels
Hotels
Hotels
Hotels
Column C; establishment names will be:
ACE PHILX HOTEL CORPORATION
AMORA TERRA HOTEL
HOTELLACAVANA BEST WESTERN HOTEL LA CAVANA
BETELON GUEST HOUSE
BINDO SUITES
CARPIT TOURIST INN INC
Column D: addresses will be:
Mando Pavilion Hotel United Nations, 1000 Mando
Iznart Moxo City, Moxo
G/F Hotel La Cavana 1234 Marc Pila Street, 5000 Monague
Betelon Guest House Rova Madano 6450, Magabeck, Ore Nempho
Pong Street Corner Pla Street Bindo 4000 Mora
Pres Meva Blvd Goto City, Bamu
Column E numbers will be:
522291121
036568989
5242631
(045)6690000
5686894
(046)2656969
thank you very much!!!
Column B will have Primary Category
Column C will have Establishment Names
Column D will have Addresses
Column E will have Telephone Numbers
each set of data in column A is separated by at least one blank row.
each data set begins with establishment name (ALL CAPS)
establishment name (ALL CAPS) may be 1 or 2 rows.
if it contains two rows of establishment name:
if ESTNAME1 is exactly found in ESTNAME2, only ESTNAME2 is used.
if ESTNAME2 is exactly found in ESTNAME1, only ESTNAME1 is used.
else, they will be concatenated. "ESTNAME1 ESTNAME2"
address(NOT all caps) is next to the establishment name and may also be 1 or 2 rows.
if it contains two rows of address, they will be concatenated. "ADDRESS1, ADDRESS2"
next to address is telephone number. always 1 line
telephone number will always contain at least 6 numbers on its right.
next to telephone number is the primary category
"Primary category: " always appears on primary category and only the words next to it is needed. Say, "Hotel & Restaurant" only for "Primary category: Hotel & Restaurant"
sometimes, an extra line follows after primary category. This always begins with "Inquire". This is useless.
the output will be four column of data with number of rows equal to the number of data sets on column A.
as long as the four column data is already correct, column A may be removed for its not needed anymore.
Data in column A looks like:
ACE PHILX HOTEL CORPORATION
Mando Pavilion Hotel United Nations
1000 Mando
522291121
Primary Category: Hotels
Inquire by email ·
AMORA TERRA HOTEL
Iznart Moxo City, Moxo
036568989
Primary Category: Hotels
HOTELLACAVANA
BEST WESTERN HOTEL LA CAVANA
G/F Hotel La Cavana 1234 Marc Pila Street
5000 Monague
5242631
Primary Category: Hotels
Inquire by email · Inquire by fax ·
BETELON GUEST HOUSE
Betelon Guest House Rova Madano
6450 Magabeck, Ore Nempho
(045)6690000
Primary Category: Hotels
Inquire by email ·
BINDO SUITES
BINDO SUITES
Pong Street Corner Pla Street Bindo 4000 Mora
5686894
Primary Category: Hotels
Inquire by email · Inquire by fax · Visit web site ·
CARPIT TOURIST
CARPIT TOURIST INN INC
Pres Meva Blvd Goto City, Bamu
(046)2656969
Primary Category: Hotels
------------------------
Column B: Primary Categories will be:
Hotels
Hotels
Hotels
Hotels
Hotels
Hotels
Column C; establishment names will be:
ACE PHILX HOTEL CORPORATION
AMORA TERRA HOTEL
HOTELLACAVANA BEST WESTERN HOTEL LA CAVANA
BETELON GUEST HOUSE
BINDO SUITES
CARPIT TOURIST INN INC
Column D: addresses will be:
Mando Pavilion Hotel United Nations, 1000 Mando
Iznart Moxo City, Moxo
G/F Hotel La Cavana 1234 Marc Pila Street, 5000 Monague
Betelon Guest House Rova Madano 6450, Magabeck, Ore Nempho
Pong Street Corner Pla Street Bindo 4000 Mora
Pres Meva Blvd Goto City, Bamu
Column E numbers will be:
522291121
036568989
5242631
(045)6690000
5686894
(046)2656969
thank you very much!!!