Query in excel

A

Ashwini

Hi,

Accurate Engineering Works
Contact Address: Near Canal Bridge, Dugri Road, Ludhiana - 141002 Punjab
Phone : +91-161-2492554/2490553 Fax : +91-161-2492555
Email : (e-mail address removed)
Web : http://www.accurate-group.com
Agrasen Engineering Industries Ltd
Contact Address: G-516 to 518, Road No: 9A, Vishwakarma Industrial Area,
Jaipur - 302013 Rajasthan
Phone : +91-141-2331716 Fax : +91-141-2332563
Email : (e-mail address removed)
Web : http://www.agrasenengg.com/
Alien Fabricon
Contact Address: 1/B, Subhash Industrial Estate,Khokhara, Ramol Road,
Ahmedabad - 380026 Gujarat
Phone : +91-79-25855892 Fax : +91-79-25855890
Email : (e-mail address removed)
Web : http://www.bottlingplantconveyor.com
Kolkata Branch: Wellesley House, South Gate, 7, Ground Floor, Red Cross
Place, Kolkata - 700001 West Bengal
Phone : +91-33-22306802/22489805 Fax : +91-33-22420868
Email : (e-mail address removed)
Web : http://www.growel.com


The above list, consist in rows, Email ID & Web mail are not in all
companies. I have totally more than 500 companies, how to segregate into
Name, Address, Phone, Email & Web mail in columns.

Can you help me?

Regards,
Ashwini
 
K

Kassie

When you say exist in rows, is it all in one cell, varying cells, or what?
If in one cell, does each cell contain the words Address:, Phone:, Fax:, E
Mail:, web:, etc?


--
HTH

Kassie

Replace xxx with hotmail
 
A

Ashwini

Hi,

It's in different rows.

Ashwini

Kassie said:
When you say exist in rows, is it all in one cell, varying cells, or what?
If in one cell, does each cell contain the words Address:, Phone:, Fax:, E
Mail:, web:, etc?


--
HTH

Kassie

Replace xxx with hotmail
Click to expand...
 
K

Kassie

Let me try again. If you say in one row per entry, is one customer record in
one cell, or in more than one cell in that row. Also, does each entry then
contain the key words Address:, Phone: etc.

--
HTH

Kassie

Replace xxx with hotmail
 
A

Ashwini

D1 : ABM Tools
D2: Contact Address: 79A, Pocket GG-1, Vikaspuri, New Delhi - 110018 Delhi
D3: Phone : + 91-11-28546891 Fax : +91-11-28540898
D4: Email : (e-mail address removed)
D5:Web : http://www.abmtools.com
D6:Growel
D7:Contact Address: Akurli Road, Kandivali(East), Mumbai - 400101 Maharastra
D8:phone : +91-22-66993200 Fax : +91-22-66993030
D9:Web : http://www.growel.com

Email ID does most of the companies does not have.

Regards,
 
K

Kassie

Thanks. When you say most companies do not have email and web addresses,
does that mean that records can contain up to 2 rows less than the ones that
do have? It would appear like that. :)
Best way would be to use a macro to do the move for you, just want to
clarify the varying numbers of rows per entry first.

--
HTH

Kassie

Replace xxx with hotmail
 
A

Ashwini

Web address are there for all the companies, but only email ID does not exist
in the companies.


Ashwini
 
K

Kassie

OK, try the following:
Press <Alt><F11>
Click on Insert, Module
Paste the following into the module's code window

Const cContact As Long = 17
Const cPhone As Long = 9
Const cFax As Long = 8
Const cEmail As Long = 8
Const cWeb As Long = 6
Dim vName As Variant, sAddress As Variant
Dim vContact As Variant, vPhone As Variant, vFax As Variant, vEmail As
Variant, vWeb As Variant



Sub TextToCol()
Range("D1").Activate
sAddress = Selection.Offset(0, 1).Address(False, False)
Transfer
End Sub

Sub Tester()
If ActiveCell.Value <> "" Then
Transfer
Else
Columns("D:D").Select
Selection.EntireColumn.Hidden = True
Exit Sub
End If
End Sub
Sub Transfer()
vName = ActiveCell.Value
ActiveCell.Offset(1, 0).Activate
vContact = Right(ActiveCell, Len(ActiveCell) - cContact)
vPhone = Mid(ActiveCell.Offset(1, 0), cPhone, 16)
vFax = Right(ActiveCell.Offset(1, 0), 16)
If Left(ActiveCell.Offset(2, 0), cEmail) = "Email : " Then
vEmail = Mid(ActiveCell.Offset(2, 0), cEmail,
Len(ActiveCell.Offset(3, 0)) - cEmail)
ActiveCell.Offset(3, 0).Activate
vWeb = Right(ActiveCell, Len(ActiveCell) - cWeb)
Range(sAddress).Value = vName
Range(sAddress).Offset(0, 1).Value = vContact
Range(sAddress).Offset(0, 2).Value = vPhone
Range(sAddress).Offset(0, 3).Value = vFax
Range(sAddress).Offset(0, 4).Value = vEmail
Range(sAddress).Offset(0, 5).Value = vWeb
sAddress = Range(sAddress).Offset(1, 0).Address(False, False)
ActiveCell.Offset(1, 0).Activate
Else
ActiveCell.Offset(2, 0).Activate
vWeb = Right(ActiveCell, Len(ActiveCell) - cWeb)
Range(sAddress).Value = vName
Range(sAddress).Offset(0, 1).Value = vContact
Range(sAddress).Offset(0, 2).Value = vPhone
Range(sAddress).Offset(0, 3).Value = vFax
Range(sAddress).Offset(0, 5).Value = vWeb
sAddress = Range(sAddress).Offset(1, 0).Address(False, False)
ActiveCell.Offset(1, 0).Activate
End If
Tester
End Sub

Close the VBA window.
Press<Alt><F8>
Select TextToCol

I wrote it so that it will not delete your original data, but will merely
hide the column. If you are satisfied with the result, you can of course
delete Col D
The macro also do not create column headers, such as Name, Address, Phone,
fax etx, you will do that.

--
HTH

Kassie

Replace xxx with hotmail
 
A

Ashwini

When I run a macro, it's showing error as

"
Compile Error:
Expected: New or type name
"

Ashwini
 
K

Kassie

I notice in your reply that line wrapping is different to that in my original
post. You must understand that what needs to be on one line, will only work
when it is on one line. To assist you, I have therefore contained coding per
line in square brackets. Compare with the code in your VBA window, and
ensure that that is the case, of course without the square brackets.
The first line that should be on one line is the following:

[Dim vContact As Variant, vPhone As Variant, vFax As Variant, vEmail As
Variant, vWeb As Variant]
Then
[ vEmail = Mid(ActiveCell.Offset(2, 0), cEmail,
Len(ActiveCell.Offset(3, 0)) - cEmail)]

Fix those two lines - just position your cursor at the end of the first
line, and press <Del> to bring the second line back - and the code should
work. I tested it on my side.

--
HTH

Kassie

Replace xxx with hotmail
 
A

Ashwini

I have not got the solution yet.

Any way Thank you for your support.

Kassie said:
I notice in your reply that line wrapping is different to that in my original
post. You must understand that what needs to be on one line, will only work
when it is on one line. To assist you, I have therefore contained coding per
line in square brackets. Compare with the code in your VBA window, and
ensure that that is the case, of course without the square brackets.
The first line that should be on one line is the following:

[Dim vContact As Variant, vPhone As Variant, vFax As Variant, vEmail As
Variant, vWeb As Variant]
Then
[ vEmail = Mid(ActiveCell.Offset(2, 0), cEmail,
Len(ActiveCell.Offset(3, 0)) - cEmail)]

Fix those two lines - just position your cursor at the end of the first
line, and press <Del> to bring the second line back - and the code should
work. I tested it on my side.

--
HTH

Kassie

Replace xxx with hotmail


Ashwini said:
When I run a macro, it's showing error as

"
Compile Error:
Expected: New or type name
"

Ashwini
Click to expand...
Click to expand...
 
K

Kassie

Can you elaborate?

--
HTH

Kassie

Replace xxx with hotmail


Ashwini said:
I have not got the solution yet.

Any way Thank you for your support.

Kassie said:
I notice in your reply that line wrapping is different to that in my original
post. You must understand that what needs to be on one line, will only work
when it is on one line. To assist you, I have therefore contained coding per
line in square brackets. Compare with the code in your VBA window, and
ensure that that is the case, of course without the square brackets.
The first line that should be on one line is the following:

[Dim vContact As Variant, vPhone As Variant, vFax As Variant, vEmail As
Variant, vWeb As Variant]
Then
[ vEmail = Mid(ActiveCell.Offset(2, 0), cEmail,
Len(ActiveCell.Offset(3, 0)) - cEmail)]

Fix those two lines - just position your cursor at the end of the first
line, and press <Del> to bring the second line back - and the code should
work. I tested it on my side.

--
HTH

Kassie

Replace xxx with hotmail


Ashwini said:
When I run a macro, it's showing error as

"
Compile Error:
Expected: New or type name
"

Ashwini

:

OK, try the following:
Press <Alt><F11>
Click on Insert, Module
Paste the following into the module's code window

Const cContact As Long = 17
Const cPhone As Long = 9
Const cFax As Long = 8
Const cEmail As Long = 8
Const cWeb As Long = 6
Dim vName As Variant, sAddress As Variant
Dim vContact As Variant, vPhone As Variant, vFax As Variant, vEmail As
Variant, vWeb As Variant



Sub TextToCol()
Range("D1").Activate
sAddress = Selection.Offset(0, 1).Address(False, False)
Transfer
End Sub

Sub Tester()
If ActiveCell.Value <> "" Then
Transfer
Else
Columns("D:D").Select
Selection.EntireColumn.Hidden = True
Exit Sub
End If
End Sub
Sub Transfer()
vName = ActiveCell.Value
ActiveCell.Offset(1, 0).Activate
vContact = Right(ActiveCell, Len(ActiveCell) - cContact)
vPhone = Mid(ActiveCell.Offset(1, 0), cPhone, 16)
vFax = Right(ActiveCell.Offset(1, 0), 16)
If Left(ActiveCell.Offset(2, 0), cEmail) = "Email : " Then
vEmail = Mid(ActiveCell.Offset(2, 0), cEmail,
Len(ActiveCell.Offset(3, 0)) - cEmail)
ActiveCell.Offset(3, 0).Activate
vWeb = Right(ActiveCell, Len(ActiveCell) - cWeb)
Range(sAddress).Value = vName
Range(sAddress).Offset(0, 1).Value = vContact
Range(sAddress).Offset(0, 2).Value = vPhone
Range(sAddress).Offset(0, 3).Value = vFax
Range(sAddress).Offset(0, 4).Value = vEmail
Range(sAddress).Offset(0, 5).Value = vWeb
sAddress = Range(sAddress).Offset(1, 0).Address(False, False)
ActiveCell.Offset(1, 0).Activate
Else
ActiveCell.Offset(2, 0).Activate
vWeb = Right(ActiveCell, Len(ActiveCell) - cWeb)
Range(sAddress).Value = vName
Range(sAddress).Offset(0, 1).Value = vContact
Range(sAddress).Offset(0, 2).Value = vPhone
Range(sAddress).Offset(0, 3).Value = vFax
Range(sAddress).Offset(0, 5).Value = vWeb
sAddress = Range(sAddress).Offset(1, 0).Address(False, False)
ActiveCell.Offset(1, 0).Activate
End If
Tester
End Sub

Close the VBA window.
Press<Alt><F8>
Select TextToCol

I wrote it so that it will not delete your original data, but will merely
hide the column. If you are satisfied with the result, you can of course
delete Col D
The macro also do not create column headers, such as Name, Address, Phone,
fax etx, you will do that.

--
HTH

Kassie

Replace xxx with hotmail


:

Web address are there for all the companies, but only email ID does not exist
in the companies.


Ashwini

:

Thanks. When you say most companies do not have email and web addresses,
does that mean that records can contain up to 2 rows less than the ones that
do have? It would appear like that. :)
Best way would be to use a macro to do the move for you, just want to
clarify the varying numbers of rows per entry first.

--
HTH

Kassie

Replace xxx with hotmail


:

D1 : ABM Tools
D2: Contact Address: 79A, Pocket GG-1, Vikaspuri, New Delhi - 110018 Delhi
D3: Phone : + 91-11-28546891 Fax : +91-11-28540898
D4: Email : (e-mail address removed)
D5:Web : http://www.abmtools.com
D6:Growel
D7:Contact Address: Akurli Road, Kandivali(East), Mumbai - 400101 Maharastra
D8:phone : +91-22-66993200 Fax : +91-22-66993030
D9:Web : http://www.growel.com

Email ID does most of the companies does not have.

Regards,

:

Let me try again. If you say in one row per entry, is one customer record in
one cell, or in more than one cell in that row. Also, does each entry then
contain the key words Address:, Phone: etc.

--
HTH

Kassie

Replace xxx with hotmail


:

Hi,

It's in different rows.

Ashwini

:

When you say exist in rows, is it all in one cell, varying cells, or what?
If in one cell, does each cell contain the words Address:, Phone:, Fax:, E
Mail:, web:, etc?


--
HTH

Kassie

Replace xxx with hotmail


:

Hi,

Accurate Engineering Works
Contact Address: Near Canal Bridge, Dugri Road, Ludhiana - 141002 Punjab
Phone : +91-161-2492554/2490553 Fax : +91-161-2492555
Email : (e-mail address removed)
Web : http://www.accurate-group.com
Agrasen Engineering Industries Ltd
Contact Address: G-516 to 518, Road No: 9A, Vishwakarma Industrial Area,
Jaipur - 302013 Rajasthan
Phone : +91-141-2331716 Fax : +91-141-2332563
Email : (e-mail address removed)
Web : http://www.agrasenengg.com/
Alien Fabricon
Contact Address: 1/B, Subhash Industrial Estate,Khokhara, Ramol Road,
Ahmedabad - 380026 Gujarat
Phone : +91-79-25855892 Fax : +91-79-25855890
Email : (e-mail address removed)
Web : http://www.bottlingplantconveyor.com
Kolkata Branch: Wellesley House, South Gate, 7, Ground Floor, Red Cross
Place, Kolkata - 700001 West Bengal
Phone : +91-33-22306802/22489805 Fax : +91-33-22420868
Email : (e-mail address removed)
Web : http://www.growel.com


The above list, consist in rows, Email ID & Web mail are not in all
companies. I have totally more than 500 companies, how to segregate into
Name, Address, Phone, Email & Web mail in columns.

Can you help me?

Regards,
Ashwini
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Query in excel 2
VBA Macro Training in Chennai 0
Outlook add-in that captures contacts from email, web page anddocuments and adds them to outlook add 0
Importing .txt file into Excel 3
#Name error in Query 18
Walk in Ahmedabad, vb, Crystal, Testing, Support 0
outlook 2007 prints contacts not in the current view 4
BUSINESS DIRECTORIES FOR TARGET MARKETING 0

Top