Forum Discussion
Parsing excel strings with variable delimiters and variable lengths
- I found my error. R was set as LONG, should have been string. My error in typing, not yours.
Now it works great!
Thanks a ton! I owe you one!
Here is a custom VBA function:
Function ExtractIt(s As String) As String
Dim i As Long
Dim p As Long
Dim r As String
i = 1
Do While i <= Len(s)
Select Case Mid(s, i, 1)
Case ",", "~"
p = i + 1
Case ":"
If Mid(s, i, 2) = ":O" Then
r = r & ", " & Mid(s, p, i - p)
End If
End Select
i = i + 1
Loop
If r <> "" Then
ExtractIt = Mid(r, 3)
End If
End FunctionUse like this:
=ExtractIt(A2)
This formula can be filled down.
Thanks that was quick!
Below is my copy from my VBA of your custom function code.
In adjacent bland cell I type: =extractit(e2) but I get the error:
I get the #NAME? error and I checked the formula name for typo, but it looks correct and I don't see a syntax error. Any idea?
Function ExtractIt(s As String) As String
Dim i As Long
Dim p As Long
Dim r As Long
i = 1
Do While i <= Len(s)
Select Case Mid(s, i, 1)
Case ",", "~"
p = i + 1
Case ":"
If Mid(s, i, 2) = ":O" Then
r = r & ", " & Mid(s, p, i - p)
End If
End Select
i = i + 1
Loop
If r <> "" Then
ExtractIt = Mid(r, 3)
End If
End Function
Did you copy the macro into a standard module (the kid you create by selecting Insert > Module in the Visual Basic Editor)? It won't work if yo copy it into a worksheet module or into ThisWorkbook.
- Ok, that I did not know!
I inserted and now when I call the function is does show up in the autocomplete, but it now throws the #VALUE! error.
=extractit(E5)- I found my error. R was set as LONG, should have been string. My error in typing, not yours.
Now it works great!
Thanks a ton! I owe you one!