Extract part of text

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

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

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.


"MLS" wrote in message
...
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

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?

"Allen Browne" wrote:

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

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.


"MLS" wrote in message
...
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

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



"MLS" wrote:

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?

"Allen Browne" wrote:

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

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.


"MLS" wrote in message
...
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

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

"Dale Fye" wrote:

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



"MLS" wrote:

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?

"Allen Browne" wrote:

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

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.


"MLS" wrote in message
...
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

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

"MLS" wrote:

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

"Dale Fye" wrote:

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



"MLS" wrote:

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?

"Allen Browne" wrote:

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

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.


"MLS" wrote in message
...
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