split

[Rami]

Hi there

can you help me split this text into 5 columns

ab_cde_fgh_ijk_lm and in the same column it could also be
ab_cde_fgh_ij_kl

the spliter will always be "_"

is there a way other than left() right() and mid()

 

[Douglas J. Steele]

There's a built in function called (coincidentally) Split.

Split("ab_cde_fgh_ijk_lm", "_") will create a five element array, with
element 0 being ab, element 1 being cde, element 2 being fgh, element 3
being ijk and element 4 being lm.

Create a function like:

Function MySplit(InputField As String, Position As Long) As String
MySplit = Split(InputField, "_")(Position)
End Function

Create a query like:

SELECT MySplit([MyField], 0) As Field0, MySplit([MyField], 1) As Field1, ...
FROM MyTable

 

[Duane Hookom]

You can use the Split() function. Split returns a zero based array.

Split("ab_cde_fgh_ijk_lm","_")(0) ="ab"
Split("ab_cde_fgh_ijk_lm","_")(3) ="ijk"
Split("ab_cde_fgh_ijk_lm","_")(5) = error... subscript out of range

 

[Dale Fye]

Rami,

I have a function that I use to parse data like this. You can use it in a
query like:

SELECT field1, ParseText([Field1], 1, "_") as Expr1,
ParseText([Field1], 2, "_") as Expr2,
ParseText([Field1], 3, "_") as Expr3,
ParseText([Field1], 4, "_") as Expr4
ParseText([Field1], 4, "_") as Expr5
FROM yourTable

If the field passed to the function does not contain 5 segments, the extra
segments will be null.

Public Function fnParseText(ByVal SomeValue As Variant, _
Position As Integer, _
Optional ParseOn As String = " ") As
Variant

Dim aArray() As String

'If the value passed was NULL, return a NULL
If IsNull(SomeValue) Then
fnParseText = Null
Exit Function
End If

SomeValue = Trim(SomeValue)

aArray = Split(SomeValue, ParseOn)

Position = Position - 1
If Position < LBound(aArray) Or Position > UBound(aArray) Then
fnParseText = Null
Else
fnParseText = aArray(Position)
End If

End Function

 

[Rami]

Thank you guys for the help, cheers and have a nice weekend

Rami,

I have a function that I use to parse data like this. You can use it in a
query like:

SELECT field1, ParseText([Field1], 1, "_") as Expr1,
ParseText([Field1], 2, "_") as Expr2,
ParseText([Field1], 3, "_") as Expr3,
ParseText([Field1], 4, "_") as Expr4
ParseText([Field1], 4, "_") as Expr5
FROM yourTable

If the field passed to the function does not contain 5 segments, the extra
segments will be null.

Public Function fnParseText(ByVal SomeValue As Variant, _
Position As Integer, _
Optional ParseOn As String = " ") As
Variant

Dim aArray() As String

'If the value passed was NULL, return a NULL
If IsNull(SomeValue) Then
fnParseText = Null
Exit Function
End If

SomeValue = Trim(SomeValue)

aArray = Split(SomeValue, ParseOn)

Position = Position - 1
If Position < LBound(aArray) Or Position > UBound(aArray) Then
fnParseText = Null
Else
fnParseText = aArray(Position)
End If

End Function

----
HTH
Dale

Hi there

can you help me split this text into 5 columns

ab_cde_fgh_ijk_lm and in the same column it could also be
ab_cde_fgh_ij_kl

the spliter will always be "_"

is there a way other than left() right() and mid()