VBA/Macro Code Problem

[marc5354]

Hello experts,

I having problem in in my Address book tracker, here i want to split all the data in different columns. like City Name, Address Post Code Phone Code.. I trimmed data in all respective column(s). Can any excellent master help me with their excellence. we need to change the code, but i don't know where to fix the problem and how. So please help me.

Please find the attached file for your reference...

...Marc

[JBeaucaire]

I can already see the next post saying whatever solution we offer doesn't work because your data varies. Your sample sheet shows one set of data. Will ALL data sets be exactly 4 rows with name first, addresses next and phone number last? They will all be identical in layout? You sure?

Maybe you need to update the sample data to show 4-5 samples with all variations demonstrated.

If it's really as simple as you've shown, you may not need VBA at all, some formulas can do the work.

[marc5354]

Hello JBeaucaire,

Yes i m sure, all datum in same order, as Name, Address, City, State, And Phone #., And finally i want to remove the duplicate data from phone number. please find the attachment.

...Marc

[JBeaucaire]

In C2:
=INDEX($B:$B, ((ROW(A1)-1)*4)+1)

In D2:
=INDEX($B:$B, ((ROW(A1)-1)*4)+2)

In F2:
=LEFT(INDEX($B:$B, ((ROW(A1)-1)*4)+3), FIND(",", INDEX($B:$B, ((ROW(A1)-1)*4)+3))-1)

In G2:
=RIGHT(INDEX($B:$B, ((ROW(A1)-1)*4)+3), 5)

In H2:
=LEFT(INDEX($B:$B, ((ROW(A1)-1)*4)+4),14)

Now copy all those cells down until you get all your values. Delete any rows with errors at the end.

Now copy the new columns and Edit > Paste Special > Values and you're done. You can delete the original data.

[JBeaucaire]

You have password protected your macros in your workbook.

[marc5354]

Oh, i am sorry

password is edit.

...Marc

[JBeaucaire]

If that takes care of your need, please click EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED.

[marc5354]

Yes boss, Problem has been fixed by you..

Thank you very much.

...Marc