Extract part of text

[MLS]

Hi,

I need to get the following data extracted into 5 different columns with
each portion being defined by a "."

PRZJFY.US.P.Coord Inside Sales.SALES
HAPJNF.GB.SM.Director E-Commerce.MARKETING

Column1:
PRZJFY
HAPJNF

Column2:
US
GB

Column3:
P
SM

Column 4:
Coord Inside Sales
Director E-Commerce

Column5:
SALES
MARKETING

I am trying to use the LEFT/MID/RIGHT functions, but can't figure out how to
work with the different data length.

Can anyone help?

Many thanks,
Marie Louise

 

[Allen Browne]

Write a VBA function using Split() to parse the text into an array.

 

[MLS]

Hi Allen,

Thanks for your reply. I'm not that familiar with VBA so wanted to see if
there was a solution I could use in Query.

Any idea on this?

 

[Dale Fye]

You could do it in a query, but it would get ugly, fast. Better to use a
function.

Copy the following code into a standard code module (not tied to a form):

Public Function fnSplitPart(SomeValue As Variant, Delimiter As String, Part
As Integer) As Variant

Dim MyArray() As String
Dim strValue As String

If IsNull(SomeValue) Then
fnSplitPart = Null
Else
strValue = CStr(SomeValue)
MyArray = Split(strValue, Delimiter)
If UBound(MyArray) + 1 < Part Then
fnSplitPart = Null
Else
fnSplitPart = MyArray(Part - 1)
End If
End If

End Function

Then, in your query you can use:

Col1:fnSplitPart([FieldName], ".", 1)
Col2:fnSplitPart([FieldName], ".", 2)

 

[MLS]

Hi Dale,

I get an error so I must be doing something wrong.

I went into Modules, opened up a new one and pasted your code in there. The
top line comes out red:

Public Function fnSplitPart(SomeValue As Variant, Delimiter As String, Part
As Integer) As Variant

I then get a syntax error when I try to save my query. Any idea what I might
be doing wrong?

Marie Louise

You could do it in a query, but it would get ugly, fast. Better to use a
function.

Copy the following code into a standard code module (not tied to a form):

Public Function fnSplitPart(SomeValue As Variant, Delimiter As String, Part
As Integer) As Variant

Dim MyArray() As String
Dim strValue As String

If IsNull(SomeValue) Then
fnSplitPart = Null
Else
strValue = CStr(SomeValue)
MyArray = Split(strValue, Delimiter)
If UBound(MyArray) + 1 < Part Then
fnSplitPart = Null
Else
fnSplitPart = MyArray(Part - 1)
End If
End If

End Function

Then, in your query you can use:

Col1:fnSplitPart([FieldName], ".", 1)
Col2:fnSplitPart([FieldName], ".", 2)

----
HTH
Dale

Hi Allen,

Thanks for your reply. I'm not that familiar with VBA so wanted to see if
there was a solution I could use in Query.

Any idea on this?

 

[MLS]

Hi Dale,

I got it to work now. I'm not so experienced with using code, but realised
that when I pasted the top part of the code into the module it had gone onto
two lines. But when I removed the line break after the first line it stopped
being red.

It now works, so thanks very much!

Marie Louise

Hi Dale,

I get an error so I must be doing something wrong.

I went into Modules, opened up a new one and pasted your code in there. The
top line comes out red:

Public Function fnSplitPart(SomeValue As Variant, Delimiter As String, Part
As Integer) As Variant

I then get a syntax error when I try to save my query. Any idea what I might
be doing wrong?

Marie Louise

You could do it in a query, but it would get ugly, fast. Better to use a
function.

Copy the following code into a standard code module (not tied to a form):

Public Function fnSplitPart(SomeValue As Variant, Delimiter As String, Part
As Integer) As Variant

Dim MyArray() As String
Dim strValue As String

If IsNull(SomeValue) Then
fnSplitPart = Null
Else
strValue = CStr(SomeValue)
MyArray = Split(strValue, Delimiter)
If UBound(MyArray) + 1 < Part Then
fnSplitPart = Null
Else
fnSplitPart = MyArray(Part - 1)
End If
End If

End Function

Then, in your query you can use:

Col1:fnSplitPart([FieldName], ".", 1)
Col2:fnSplitPart([FieldName], ".", 2)

----
HTH
Dale

Hi Allen,

Thanks for your reply. I'm not that familiar with VBA so wanted to see if
there was a solution I could use in Query.

Any idea on this?

:

Write a VBA function using Split() to parse the text into an array.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.


Hi,

I need to get the following data extracted into 5 different columns with
each portion being defined by a "."

PRZJFY.US.P.Coord Inside Sales.SALES
HAPJNF.GB.SM.Director E-Commerce.MARKETING

Column1:
PRZJFY
HAPJNF

Column2:
US
GB

Column3:
P
SM

Column 4:
Coord Inside Sales
Director E-Commerce

Column5:
SALES
MARKETING

I am trying to use the LEFT/MID/RIGHT functions, but can't figure out how
to
work with the different data length.

Can anyone help?

Many thanks,
Marie Louise