Macro for text to columns function

[Lauren_M]

Hey everyone,
Hoping that someone may be able to help me in writing a macro for text to columns function (if this is the best way to go).
Our work report exports customer subscriptions which we call "role name". Our customers subscriptions have a valid and start date, but our customers also can purchase multiple subscriptions and we need to separate this data in order to report by subscription.

Our export has an * after each subscription, the date is in square brackets and each subscription is separated by a semi column.

So for arguments sake, let's say we have the following subscriptions:
subscription a
subscription B
subscription C
subscription D

if one customer has purchased four subscriptions on various dates, their exports may look like this:
Subscription A* [01/02/2012 12:00:00 AM]* [01/02/2013 12:00:00AM]; Subscription B* [01/03/2012 12:00:00 AM]* [01/03/2013 12:00:00AM]; Subscription C* [01/04/2012 12:00:00 AM]* [01/04/2013 12:00:00AM]; Subscription D* [01/05/2012 12:00:00 AM]* [01/05/2013 12:00:00AM]
I then use a text to columns function to separate by the * and the;
I then am left with a column for the role, one for the start date and one for the expiry date.
The roles are in alphabetical order, but sometimes some manual sorting needs to be done to delete the roles I don’t need and keep the ones I do. For example someone could have subscribed to subscription B and not in A that means that the first column may not have all the roles I need, it may be in the second or third column depending on what other subscriptions they have.
So not sure if text to column is even the best way to go???
Hoping this all makes sense and someone can suggest/help with a quick way to do this.
Thanks in advance for anyone that is willing to tackle this J

 

[James006]

Hi,

In order to help you out in an efficient way, I do think that sharing a sample worksheet with the forum would contribute a lot ...

 

[Lauren_M]

Silly question? How to I upload a sample worksheet?

 

[Peter_SSs]

Silly question? How to I upload a sample worksheet?

You can't upload a file here but it is usually sufficient to post small 'before' and 'after' screen shots here in your post.
My signature block has suggestions for that.

For the Text to Columns part, try something like this in a copy of your workbook.

Sub TTC()
  Const DataCol As String = "B"
  
  Range(DataCol & 1, Cells(Rows.Count, DataCol).End(xlUp)).TextToColumns _
    Destination:=Cells(1, DataCol).Offset(, 1), DataType:=xlDelimited, _
    TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
    Semicolon:=True, Comma:=False, Space:=False, Other:=True, OtherChar:="*"
End Sub

The latter part of your request is not that clear to me so further explanation and/or those screen shots might help.

 

[Lauren_M]

Ok, hopefully this helps make it clearer.
This is how my data exports:

Code deleted by Moderator

As you can see I require one column for the role description, one for the start date and one for the expiration date. Some users can have multiple subscriptions so I would need to continue the text to column function until all roles have been seperated.

Basically what my managers want to see if a total number of active subscriptions for each subscription. How many new for each month and how many expired. I usually get this data by generating a pivot table once I have formatted the above and using if and date formulas.
I don't know if this is the best way to do it, but it's how I know and it works...
I tend to create many small macros because I don't know how to write complex ones. I think I can record a basic macro for the above text to column function, but I am scared I may delete necessary data...

Hopefully I have explained myself a little better? Apologies for the confusion and I really appreciate everyones input!

 

[Lauren_M]

Can someone please delete the above response? Obviously my pasting a screen print isn't working properly Sorry for all the hassle, this isn't going to work for me...I'll just keep doing it the long and manual way I have been. Sorry for the trouble!!

 

[Peter_SSs]

I've fixed that image problem but I don't think you should give up so easily.

I suspect that you used the HTML maker correctly except for one thing. When you paste into your post, don't put [ IMG] tags around it. The code may look a mess when you first paste it into your post but when you 'Submit' your reply, it should turn into a nice screen shot of your sheet. If you want to have another try or two, do so in the Test Here forum. It doesn't really matter what happens there as those threads are deleted fairly regularly.