+ Reply to Thread
Results 1 to 2 of 2

VBA Script to create new rows based on delimited data

  1. #1
    Registered User
    Join Date
    01-10-2012
    Location
    Toronto, ON
    MS-Off Ver
    Excel 2007
    Posts
    1

    VBA Script to create new rows based on delimited data

    Hello everyone, I am new to the forum here. Glad to be a member - there seems to be a lot of very knowledgable members here!

    As a quick intro, I run a small telecommunications business and do quite a bit of data manipulation involving long distance rates.

    I get data in different formats from different providers and I always import the data to excel where I will change the fields around and format it exactly as my billing software expects it.

    The data I got from my latest provider is proving to be quite the challenge to get formatted correctly. I think I need a simple VBA script to do the job for me, but I have no experience at all in VBA scripting. I would greatly appreciate if someone could point me in the right direction for what I am trying to accomplish.

    Now, here is the problem:

    I need my destination prefix data as a single number. The rest of the data is irrelevant for this problem but in case it will help here is the important data I need formatted:

    | dial prefix | destination name | sell rate | buy rate |

    Usually I get data in the format of:

    | country | prefix | rate | name |
    | 1 | 416 | 0.001 | Canada |
    | 1 | 514 | 0.001 | Canada |

    This would be no problem as I can just merge the country number with the prefix to get something like:

    =B1&B2

    | dialprefix | name | rate |
    | 1416 | Canada | 0.001 |
    | 1514 | Canada | 0.001 |

    My latest provider really though me for a loop with this kind of data:

    | country | prefix | rate | name |
    | 1 | 416&905&647 | 0.001 | Canada |
    | 1 | 514&289 | 0.001 | Canada |

    Some of my international destinations have anywhere from 2 to 250+ of seperate prefixs seperated by "&" signs in a single cell. I have to have a single dialprefix on each row, so for each entry that has a prefix with 1 or more & signs I need to seperate this data and copy it to a new row. I have figured out how to seperate all the prefixes using the Text to Columns function, but this would still require a ton of manual work to keep inserting rows and then one at a time copying the prefixes in.

    In case I am not being clear enough, I will give a very clear example again:
    I also attached a small sample of Chile rates that have quite a bit of "&" seperated prefixes.

    Afghanistan:

    Afghanistan 0.23 93 70
    Afghanistan 0.2272 93 79
    Afghanistan 0.2255 93 7502&7503&7500&7501&7504


    I would instead need:

    Afghanistan 0.23 93 70
    Afghanistan 0.2272 93 79
    Afghanistan 0.2255 93 7502
    Afghanistan 0.2255 93 7503
    Afghanistan 0.2255 93 7500
    Afghanistan 0.2255 93 7501
    Afghanistan 0.2255 93 7504

    If anyone could guide me in the right direction, or hopefully already have a similar VBA script done - that would be of great assistance! And maybe I could help you get better long distance rates as well

    Thanks,

    Aaron
    PirayaTel
    www.piraya.com
    Attached Files Attached Files
    Last edited by aaronic; 01-11-2012 at 10:31 AM. Reason: Forgot attachment.

  2. #2
    Forum Guru tigeravatar's Avatar
    Join Date
    03-25-2011
    Location
    Colorado, USA
    MS-Off Ver
    Excel 2003 - 2013
    Posts
    5,361

    Re: VBA Script to create new rows based on delimited data

    aaronic,

    Welcome to the forum!

    Give this code a try:
    Please Login or Register  to view this content.
    Last edited by tigeravatar; 01-10-2012 at 06:40 PM.
    Hope that helps,
    ~tigeravatar

    Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1