If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
Extract part of text
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 |
#3
|
|||
|
|||
Extract part of text
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 |
#4
|
|||
|
|||
Extract part of text
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 |
#5
|
|||
|
|||
Extract part of text
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 |
#6
|
|||
|
|||
Extract part of text
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 |
Thread Tools | |
Display Modes | |
|
|