vba to parse column like text to columns but with a twist

[Sayre]

I have several files with contents/values in column A going down variable amounts of rows. I need a macro that can do some thing like 'Text To Columns' function where a colon, ":", is the delimiter but only on the first instance of the colon. Later on in each string there may be more colons but I don't want those to be split out.

Example: One of the values within a cell would be something like "MirrorView UID: 10:5A:A0:BE:60:01:06:50:17:00:00:00:00:00:00:00"

I want the macro to make the split on the first colon in this string only so that column A will contain "MirrorView UID" and column B will then contain "10:5A:A0:BE:60:01:06:50:17:00:00:00:00:00:00:00".

The values within each cell are of varying length and the number of rows in each file can also vary with spaces in between.

Hope this makes sense. After that I have another thing I want to do with the data but I'll describe that after. Any help on this iswould be plenty good for now thank you!

 

[Sayre]

Rick I like this second one better except: Its adding an extra colon at the end of each value in column B after it does the split. Not a huge problem but can you fix that? Your first code doesn't do that.

 

[Rick Rothstein]

Rick I like this second one better except: Its adding an extra colon at the end of each value in column B after it does the split. Not a huge problem but can you fix that? Your first code doesn't do that.

This is modeled after that second macro, but I changed some internal calculations to make it work better (still will be super fast)...

Sub SplitOnFirstColon()
  Dim X As Long, LastRow As Long, Colon As Long, vArr As Variant
  Const FirstRow As Long = 1
  LastRow = Cells(Rows.Count, "A").End(xlUp).Row
  vArr = Cells(FirstRow, "A").Resize(LastRow - FirstRow + 1, 2)
  For X = 1 To LastRow - FirstRow + 1
    Colon = InStr(vArr(X, 1) & ":", ":")
    vArr(X, 2) = Trim(Mid(vArr(X, 1), Colon + 1))
    vArr(X, 1) = Trim(Left(vArr(X, 1), Colon - 1))
  Next
  Cells(FirstRow, "A").Resize(LastRow - FirstRow + 1, 2) = vArr
End Sub

 

[István Hirsch]

As far as I can see there is a space after the first part. Is so, and macro is not a must, I would replace :_ with ß in column A, then Data/Text to Columns with ß as a delimiter.

 

[Sayre]

Rick this works great and the extra ':' is now gone. Can't thank you enough! Istvan, if I follow you correctly, you mean a space in the text string after the 1st colon? Yes you are right and it looks like that space did not get preserved with this code but I don't need it to be preserved so no problem.

 

[Rick Rothstein]

Rick this works great and the extra ':' is now gone. Can't thank you enough! Istvan, if I follow you correctly, you mean a space in the text string after the 1st colon? Yes you are right and it looks like that space did not get preserved with this code but I don't need it to be preserved so no problem.

Actually, Istvan was trying to tell you something different. He was saying that IF (he accidentally wrote is) there is always space is after the first colon, and if that is the only place there is a space after a colon, then you do not need to use a macro to split your data apart. His suggestion is to use Excel's Replace dialog box to replace the colon/space combination with a single character the will never be found in your text (his suggestion was the ß character) and then use Excel's Text-To-Columns dialog box to split the text apart using that character as the delimiter.

 

[Sayre]

Ah ok that makes sense. And yes I think there is a space ONLY after the 1st colon in each so this would have worked, at least for this data set. Glad to have your code anyway Nick, for other data sets in the future. Need many tricks in the bag for all possible scenarios...

 

[István Hirsch]

Rick, thank you very much for the correct explanation - I may have been a bit short. And yes, I wanted to write in my first post: "If so, and macro is not a must..."

 

[Peter_SSs]

One piece of sample data is not much to go on but in that one it appears that the space after the colon is the only space so a slight twist on Istvan's suggestion would be to do Text to Columns directly using the space delimiter. Perhaps the colon left on the end of the first column is not an issue for you, but if so Find/Replace on that column could remove it. Same number of steps as Istvan I know - but just thought that first colon might not be important to remove, saving one of the steps.

 

[Sayre]

Peter_SSs good point and you're right, the colon is not terribly important to remove its just the spot where I need to do the split so your solution works too. Looking through the entire data set it does appear that the space occurs right after the first colon every time. So now I have 3 solutions to choose from thanks again!