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!

 

[anvg]

An array udf function

Public Function SplitMirrorView(ByVal this As String)
    Dim sResult(1 To 2) As String, pos As Long
    pos = InStr(this, ":")
    sResult(1) = Trim$(Mid$(this, 1, pos - 1))
    sResult(2) = Trim$(Mid$(this, pos + 1))
    SplitMirrorView = sResult
End Function

Let "MirrorView UID: 10:5A:A0:BE:60:01:06:50:17:00:00:00:00:00:00:00" is in the A1 cell.
Select two cells in a row (for example B1:C1) and input =SplitMirrorView(A1) after press Ctrl+Shif+Enter keys.

 

[Sayre]

Hmm this does not seem to work right. I get the 1st part appearing in Cell B1 ("Mirror View ID") but not the 2nd part in cell C1. I was hoping for a macro that just goes down all the rows and does it all without a UDF or pressing control+shift+enter. DO you thik that;s possible?

 

[Sayre]

Or to be more specific, a loop macro. If only there were not some values with more than one colon in the string. Then I could just have the macro do a "text to columns" with a colon as the delimiter and I'd be all set. Let me know if you thik not possible, seems like it shold be though. Thanks a lot for your help.

 

[Sayre]

anvg I was able to modify what you did into something that works just right for me thanks a ton! I modified your UDF into 2 separate UDF's, one that parses out the 1st section into column B, the other parses out the 2nd section into column C. I then put both into a loop macro that inserts the udf's down all rows and does not require array (control+shift+enter).

Check it out:

Public Function SplitMirrorView(ByVal this As String)
    Dim sResult As String, pos As Long
    pos = InStr(this, ":")
    sResult = Trim$(Mid$(this, 1, pos - 1))
    SplitMirrorView = sResult
End Function
Public Function SplitMirrorView_2(ByVal this As String)
    Dim sResult As String, pos As Long
    pos = InStr(this, ":")
    sResult = Trim$(Mid$(this, pos + 1))
    SplitMirrorView_2 = sResult
End Function


Sub Split_Macro()

Dim RangeToCheck As Range, Cell As Range

Set RangeToCheck = Range("A1:A" & Cells(Rows.Count, "A").End(xlUp).Row)
For Each Cell In RangeToCheck

If Cell <> "" Then

Cell.Offset(0, 1) = "=SplitMirrorView(RC[-1])"
Cell.Offset(0, 2) = "=SplitMirrorView_2(RC[-2])"

End If

Next Cell

End Sub

Thanks again anvg!

 

[anvg]

I wrote this function for using as worksheet function on a sheet. After entering with Ctrl+Shift+Enter the function has to look like {=SplitMirrorView(A1)} in both cells B1 and C1. But, indeed, you able to modify the code of the fuction as you want and as you need.
Regards,

 

[Rick Rothstein]

This macro appears to do what you asked...

Sub SplitOnFirstColon()
  Dim LastRow As Long, AddrA As String, AddrAB As String
  Const FirstRow As Long = 2
  LastRow = Cells(Rows.Count, "A").End(xlUp).Row
  AddrA = "A" & FirstRow & ":A" & LastRow
  AddrAB = "A" & FirstRow & ":B" & LastRow
  Cells(FirstRow, "A").Resize(LastRow - FirstRow + 1, 2) = Evaluate(Replace("IF(COLUMN(" & AddrAB & _
    ")=1,TRIM(LEFT(@,FIND("":"",@&"":"")-1)),TRIM(MID(@,FIND("":"",@&"":"")+1,999)))", "@", AddrA))
End Sub

Note: You did not tell us what row your data started on, so I assumed Row 2... if this is not correct, then change the 2 in the Const statement to whatever Row number contains your first piece of data.

 

[Sayre]

Great, I'll try this one too, thanks Rick! Always good to have more than one way to do it. It starts on row 1 actually, I'll modify yours slightly.

 

[Sayre]

Rick, wow this one works like a charm, thanks again!

 

[Rick Rothstein]

Rick, wow this one works like a charm, thanks again!

You are welcome. As fast as that code is, I believe this code will be faster (possibly noticeably faster if you have a lot of data)...

Sub SplitOnFirstColon()
  Dim X As Long, LastRow As Long, vArr As Variant, Parts() As String
  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
    Parts = Split(vArr(X, 1) & ":", ":", 2)
    vArr(X, 1) = Trim(Parts(0))
    vArr(X, 2) = Trim(Parts(1))
  Next
  Cells(FirstRow, "A").Resize(LastRow - FirstRow + 1, 2) = vArr
End Sub