VBA code to separate values in a cell

[Starkey]

Hey guys

A random one for you please.

I've a worksheet that has a column that holds multiple IDs, formatted as such:

123;45;6745;123

I.e. A semi colon separates the different IDs. Does anyone know of some elegant code that can separate the values back out again and put each ID in to a separate column in an empty worksheet?

I can actually do it but I don't think my coding is very efficient and its a bit long winded

Thanks as always
Starkey

 

[Joe4]

Copy it over to the new sheet you want, highlight that column, and do a "Text To Columns" (found under the Data menu) using the semi-colon as your Delimiter. The wizard will walk you through it.

 

[Starkey]

Thanks for the reply Joe. Sorry I forgot to mention it was a VBA code solution I was after, ie clicking on a command button would do it automatically

Cheers
Starkey

 

[Joe4]

Record yourself doing it, and you have the VBA code to do it!

 

[Starkey]

Thanks for your reply again

Loving the text to columns thing, didn't know about the delimiter. Just had a look at the code - it works a treat except that hard coded in it is the number of arrays (ie the number of values that need separating out). I need to give this one some thought!

Cheers again
Starkey

 

[Joe4]

If you know the maximum number of arrays that could possibly have, you can hard-code that into the VBA code. It won't hurt anything to have more arrays in the VBA code than fields in your data.

 

[Starkey]

Yeah that's the problem I think. There is no maximum and the number could conceivably run in to many 10's of values. That's why I've concatenated the IDs together with a separator. Ordinarily I would put an ID in a separate column for each record but with no maximum number of IDs I don't want to limit the number of columns I set aside for IDs. Each record contains many different types of values and I need to understand what type of value is in each cell

Hope that makes sense! Before I'd written elaborate code that ran through the cell, character by character, until it came across a semicolon, then worked back to read the number value but that was so long winded!

Cheers for your help tonight
Starkey

 

[Joe4]

Sounds almost like you got a relational database thing working there. In that instance, you would only have one ID column, and you would have multiple rows for each ID instead of multiple columns.

Is something like that feasible?
If not, I am sure we can probably come up with code to do what you want.

 

[Starkey]

I did think of a relational database to be honest but it just means another sheet and more matching of ID values which is do-able and I have done that in some instances but because I'm self taught on VBA, I'm really starting to write much more efficient code and I just think I'm missing a trick with how I've coded this one. I know it sounds daft but whereas in the past I may have written 50 lines of code to do something, now it may only be 2 or 3 lines. Geeky I know but I like learning new things!

Cheers

 

[Joe4]

I'm self taught on VBA, I'm really starting to write much more efficient code and I just think I'm missing a trick with how I've coded this one. I know it sounds daft but whereas in the past I may have written 50 lines of code to do something, now it may only be 2 or 3 lines. Geeky I know but I like learning new things!

I know where you are coming from, and many of us are the same way.

Feel free to post your code here, and we'll see if we can give you any tips in making it more efficient.