Extract part of text

  • Thread starter Thread starter MLS
  • Start date Start date
M

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
 
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?
 
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)
 
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 said:
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 said:
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?
Click to expand...
Click to expand...
 
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 said:
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 said:
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 said:
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
Click to expand...
Click to expand...
Click to expand...
 
Back
Top