Comma delim- Parsing first, second, and third word from a field in ACCESS formula needed 1

[tav1035]

I'm looking for a formula that will parse a comma delimited field.
I think I have the first formula-> Class: IIf(InStr(1,[ASSET.DESCRIPTION],",")=0,[ASSET.DESCRIPTION],Left([ASSET.DESCRIPTION],InStr(1,[ASSET.DESCRIPTION],",")-1))
I am struggling with the other 2 formulas.

My data with 3 example rows of data-
DESCRIPTION field-
WATER HEATER, 3 PH
DEAERATOR,
DETECTOR, SMOKE, DUCT, FIRE ALARM SYSTEM

First Description-
First word should be: WATER HEATER

Second word should be: 3 PH​

Third word should be: blank​

Second Description-
First word should be: DEAERATOR

Second word should be: blank​

Third word should be: blank​

Third Description-
First word should be: DETECTOR

Second word should be: SMOKE​

Third word should be: DUCT​

Thanks tav

 

[tav1035]

Forgot to mention, i want to parse the description into 3 different columns.

 

[Andrzejek]

Can you use VBA and use Split function?

Have fun.

---- Andy

 

[tav1035]

I have never used vba in access and I can if I need to, but was trying to keep it simple with just a few formulas.
I was using Excel and now have switched to Access.
Here are the 3 formulas that I used in Excel.
TRIM(MID(SUBSTITUTE("," & $I8&REPT(",",6),",",REPT(" ",255)),1*255,255))
TRIM(MID(SUBSTITUTE("," & $I8&REPT(",",6),",",REPT(" ",255)),2*255,255))
TRIM(MID(SUBSTITUTE("," & $I8&REPT(",",6),",",REPT(" ",255)),3*255,255))

tav

 

[dhookom]

It might be time to learn some vba ;-)

Create a new blank module and paste this code into it and then save the module with the name "modStringFunctions".

Public Function ParseText(pstrText As String, intElement As Integer, _
        pstrDelimiter As String) As String
    Dim arText() As String
   On Error GoTo ParseText_Error

    arText() = Split(pstrText, pstrDelimiter)
    ParseText = arText(intElement - 1)
    
ExitParseText:
   On Error GoTo 0
   Exit Function

ParseText_Error:
    Select Case Err
        Case 9 [COLOR=#4E9A06]'subscript out of range[/color]
            [COLOR=#4E9A06]'don't do anything[/color]
        Case Else
            MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure ParseText of Module basParseText"
    End Select
    Resume ExitParseText
End Function

You can then create columns in a query with the expression
[tt]Value1: ParseText([Description],1,", ")[/tt]
[tt]Value2: ParseText([Description],2,", ")[/tt]
[tt]Value3: ParseText([Description],3,", ")[/tt]



Duane
Hook'D on Access
MS Access MVP

 

[tav1035]

dhookom, thanks for the quick response,
The vb works great and was easy to use.
thanks,
tav

 

[Andrzejek]

I am with dhookom
IMHO you can do a LOT more with VBA than with Formula

Option Explicit

Private Sub Command1_Click()
Dim str As String

str = "WATER HEATER, 3 PH"
Call SplitText(str)

str = "DEAERATOR,"
Call SplitText(str)

str = "DETECTOR, SMOKE, DUCT, FIRE ALARM SYSTEM"
Call SplitText(str)

End Sub

Private Sub SplitText(ByRef strText As String)
Dim arr() As String
Dim i As Integer

arr = Split(strText, ",")
Debug.Print "Text: " & strText
For i = LBound(arr) To UBound(arr)
    Debug.Print i & " - " & arr(i)
Next i
Debug.Print ""

End Sub

You get:[pre]
Text: WATER HEATER, 3 PH
0 - WATER HEATER
1 - 3 PH

Text: DEAERATOR,
0 - DEAERATOR
1 -

Text: DETECTOR, SMOKE, DUCT, FIRE ALARM SYSTEM
0 - DETECTOR
1 - SMOKE
2 - DUCT
3 - FIRE ALARM SYSTEM
[/pre]

Of course you can Trim it to get rid of leading spaces.

Have fun.

---- Andy