| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Query in excel
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 "Ashwini" wrote: Hi, Accurate Engineering Works Contact Address: Near Canal Bridge, Dugri Road, Ludhiana - 141002 Punjab Phone : +91-161-2492554/2490553 Fax : +91-161-2492555 Email : 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 : 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 : 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 : 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 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Query in excel
Hi,
It's in different rows. Ashwini "Kassie" wrote: 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 "Ashwini" wrote: Hi, Accurate Engineering Works Contact Address: Near Canal Bridge, Dugri Road, Ludhiana - 141002 Punjab Phone : +91-161-2492554/2490553 Fax : +91-161-2492555 Email : 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 : 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 : 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 : 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 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Query in excel
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 "Ashwini" wrote: Hi, It's in different rows. Ashwini "Kassie" wrote: 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 "Ashwini" wrote: Hi, Accurate Engineering Works Contact Address: Near Canal Bridge, Dugri Road, Ludhiana - 141002 Punjab Phone : +91-161-2492554/2490553 Fax : +91-161-2492555 Email : 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 : 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 : 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 : 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 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Query in excel
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 : 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, "Kassie" wrote: 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 "Ashwini" wrote: Hi, It's in different rows. Ashwini "Kassie" wrote: 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 "Ashwini" wrote: Hi, Accurate Engineering Works Contact Address: Near Canal Bridge, Dugri Road, Ludhiana - 141002 Punjab Phone : +91-161-2492554/2490553 Fax : +91-161-2492555 Email : 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 : 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 : 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 : 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 |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Query in excel
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 "Ashwini" wrote: 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 : 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, "Kassie" wrote: 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 "Ashwini" wrote: Hi, It's in different rows. Ashwini "Kassie" wrote: 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 "Ashwini" wrote: Hi, Accurate Engineering Works Contact Address: Near Canal Bridge, Dugri Road, Ludhiana - 141002 Punjab Phone : +91-161-2492554/2490553 Fax : +91-161-2492555 Email : 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 : 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 : 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 : 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 |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Query in excel
Web address are there for all the companies, but only email ID does not exist
in the companies. Ashwini "Kassie" wrote: 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 "Ashwini" wrote: 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 : 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, "Kassie" wrote: 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 "Ashwini" wrote: Hi, It's in different rows. Ashwini "Kassie" wrote: 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 "Ashwini" wrote: Hi, Accurate Engineering Works Contact Address: Near Canal Bridge, Dugri Road, Ludhiana - 141002 Punjab Phone : +91-161-2492554/2490553 Fax : +91-161-2492555 Email : 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 : 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 : 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 : 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 |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Query in excel
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 "Ashwini" wrote: Web address are there for all the companies, but only email ID does not exist in the companies. Ashwini "Kassie" wrote: 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 "Ashwini" wrote: 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 : 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, "Kassie" wrote: 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 "Ashwini" wrote: Hi, It's in different rows. Ashwini "Kassie" wrote: 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 "Ashwini" wrote: Hi, Accurate Engineering Works Contact Address: Near Canal Bridge, Dugri Road, Ludhiana - 141002 Punjab Phone : +91-161-2492554/2490553 Fax : +91-161-2492555 Email : 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 : 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 : 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 : 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 |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Query in excel
When I run a macro, it's showing error as
" Compile Error: Expected: New or type name " Ashwini "Kassie" wrote: 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 "Ashwini" wrote: Web address are there for all the companies, but only email ID does not exist in the companies. Ashwini "Kassie" wrote: 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 "Ashwini" wrote: 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 : 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, "Kassie" wrote: 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 "Ashwini" wrote: Hi, It's in different rows. Ashwini "Kassie" wrote: 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 "Ashwini" wrote: Hi, Accurate Engineering Works Contact Address: Near Canal Bridge, Dugri Road, Ludhiana - 141002 Punjab Phone : +91-161-2492554/2490553 Fax : +91-161-2492555 Email : 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 : 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 : 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 : 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 |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Query in excel
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" wrote: When I run a macro, it's showing error as " Compile Error: Expected: New or type name " Ashwini "Kassie" wrote: 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 "Ashwini" wrote: Web address are there for all the companies, but only email ID does not exist in the companies. Ashwini "Kassie" wrote: 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 "Ashwini" wrote: 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 : 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, "Kassie" wrote: 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 "Ashwini" wrote: Hi, It's in different rows. Ashwini "Kassie" wrote: 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 "Ashwini" wrote: Hi, Accurate Engineering Works Contact Address: Near Canal Bridge, Dugri Road, Ludhiana - 141002 Punjab Phone : +91-161-2492554/2490553 Fax : +91-161-2492555 Email : 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 : 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 : 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 : 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 |
|
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Query in excel
I have not got the solution yet.
Any way Thank you for your support. "Kassie" wrote: 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" wrote: When I run a macro, it's showing error as " Compile Error: Expected: New or type name " Ashwini "Kassie" wrote: 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 "Ashwini" wrote: Web address are there for all the companies, but only email ID does not exist in the companies. Ashwini "Kassie" wrote: 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 "Ashwini" wrote: 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 : 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, "Kassie" wrote: 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 "Ashwini" wrote: Hi, It's in different rows. Ashwini "Kassie" wrote: 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 "Ashwini" wrote: Hi, Accurate Engineering Works Contact Address: Near Canal Bridge, Dugri Road, Ludhiana - 141002 Punjab Phone : +91-161-2492554/2490553 Fax : +91-161-2492555 Email : 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 : 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 : 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 : 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 |
|
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Query in excel
Can you elaborate?
-- HTH Kassie Replace xxx with hotmail "Ashwini" wrote: I have not got the solution yet. Any way Thank you for your support. "Kassie" wrote: 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" wrote: When I run a macro, it's showing error as " Compile Error: Expected: New or type name " Ashwini "Kassie" wrote: 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 "Ashwini" wrote: Web address are there for all the companies, but only email ID does not exist in the companies. Ashwini "Kassie" wrote: 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 "Ashwini" wrote: 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 : 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, "Kassie" wrote: 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 "Ashwini" wrote: Hi, It's in different rows. Ashwini "Kassie" wrote: 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 "Ashwini" wrote: Hi, Accurate Engineering Works Contact Address: Near Canal Bridge, Dugri Road, Ludhiana - 141002 Punjab Phone : +91-161-2492554/2490553 Fax : +91-161-2492555 Email : 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 : 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 : 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 : 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Excel 2007 / MS Query - editing existing query to another sheet | Excel Discussion (Misc queries) | |||
| Can I use MS Query in Excel like an Append Query in Access | Excel Discussion (Misc queries) | |||
| Anyone Else Use Database Query to Query Another Sheet in the Same Excel Workbook? | Excel Discussion (Misc queries) | |||
| Anyone Else Use Database Query to Query Another Sheet in the Same Excel Workbook? | Excel Discussion (Misc queries) | |||
| How to use a Access Query that as a parameter into Excel database query | Excel Discussion (Misc queries) | |||
Linear Mode
