Forum Discussion
Parsing excel strings with variable delimiters and variable lengths
I have large excel files with one column that I need to parse out specific words that are delimited with 3 different delimiters that are defined but are variable on 2 of them. The cells of the column contain a string of variable length. - See below
The problem I have is the occurrence in the cell is variable, the needed string is variable, and the LEFT delimiter is variable.
The LEFT delimiter is either ("," ) or ("~" )and the right delimiter is always ":O".
So, in the first cell I need to parse out, "CDIFO" (~CDIFO:O) each of the 4 occurrences and put them into a new comma delimited column to an adjacent column to the right of the column, like:
CDIFO, CDIFO, CDIFO, CDIFO
| R1#INDET,INDET2~CDIFO:O-RO^0-0~CCDIF:O-RO^0-0\R1#INDET2,INDET2~CDIFO:O-RO^0-0~CCDIF:O-RO^0-0\R1#POS,Call result and if IP fax to Infection Control 600-2735:D\R1#R1,R1 |
The second example would be to parse out the "HDL":
BT#BT,\R1#'?NUM,R1\BT#LIPSC,\R1#>190,HDL:O-RO
| R1#INDET,INDET2~CDIFO:O-RO^0-0~CCDIF:O-RO^0-0\R1#INDET2,INDET2~CDIFO:O-RO^0-0~CCDIF:O-RO^0-0\R1#POS,Call result and if IP fax to Infection Control 600-2735:D\R1#R1,R1 |
| R1#INDET2,CDIFO:O-RO^0-0~CCDIF:O-RO^0-0\R1#R1,R1 |
| R1#INDET,INDET2~CDIFO:O-RO^0-0~CCDIF:O-RO^0-0\R1#INDET2,INDET2~CDIFO:O-RO^0-0~CCDIF:O-RO^0-0\R1#POS,Call result and if IP fax to Infection Control 415-232-6474:D\R1#R1,R1 |
| R1#INDET2,CDIFO:O-RO^0-0\R1#R1,R1 |
| R1#INDET2,CDIFO:O-RO~CCDIF:O-RO\R1#R1,R1 |
| R1#INDET2,CDIFO:O-R\R1#R1,R1 |
| R1#INDET2,CDIFO:O-RO~CCDIF:O-RO\R1#POS,Call result and if In-Patient notify Infection Control:D~Please re-enter POS:P\R1#INDET2,If INDET2, Reprint accession label and take to Procesing area:D~Please re-enter INDET2:P\R1#R1,R1 |
| R1#INDET2,INDET2~CDIFO:O-RO^0-0\R1#R1,R1 |
| R1#INDET,INDET2~CDIFO:O-RO^0-0~CCDIF:O-RO^0-0\R1#INDET2,INDET2~CDIFO:O-RO^0-0~CCDIF:O-RO^0-0\R1#POS,Call result and if IP fax to Infection Control 415-232-6474:D\R1#R1,R1 |
| BT#BT,\R1#'?NUM,R1\BT#LIPSC,\R1#>190,HDL:O-RO\R1#<50,50 |
| BT#BT,\R1#'?NUM,R1\BT#LIPSC,\R1#>190,HDL:O-RO\R1#<50,50 |
| BT#BT,\R1#'?NUM,R1\BT#LIPSC,\R1#>190,HDL:O-RO\R1#<50,50 |
| BT#BT,\R1#'?NUM,R1\BT#LIPSC,\R1#>190,HDL:O-RO\R1#<50,50 |
| AN#?ENGL,\BT#LIPSC,\R1#>190,HDL:O-RO\R1#<3.0,3.0\R1#>4400.0,4400.0 |
| R1#IFLAG,^STOP\AN#'?ENGL,R1\BT#LIPSC,\R1#>190,R1~HDL:O-RO^0-0\BT#BT,\R1#'?NUM,R1|R1,R1 |
| BT#BT,\R1#9999999,^STOP\BT#LIPSC,\R1#>190,HDL:O-RO\BT#BT,\R1#'?NUM,R1|'?NUM,^STOP|<50,50|<50,^STOP|R1,R1 |
| BT#BT,\R1#'?NUM,R1\BT#LIPSC,\R1#>190,HDL:O-RO\R1#<50,50 |
| BT#BT,\R1#'?NUM,R1\BT#LIPSC,\R1#>190,ORDERING REFLEXIVE HDL:D|>190,HDL:O-RO |
| BT#BT,\R1#'?NUM,R1\BT#LIPSC,\R1#>190,HDL:O-RO |
| BT#BT,\R1#'?NUM,R1\BT#LIPSC,\R1#>190,ORDERING REFLEXIVE HDL:D|>190,HDL:O-RO |
- 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!
8 Replies
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 FunctionDid 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.
