help me in , separate a text to several columns ?

[evilleve]

i have a field that contain string , like this :
(15-SW-037[SMAW]#0~300,RS,RES)
(15-SW-042[SMAW]#0~0,GM,RET#1600~1700,SL,REP)
etc ....
i want to split them to several fields : field1 :15-sw-037 Field 2 : [smaw] Field 3 : 0~0 ,etc ....

is there any way to do this ?



thanks in advance

[RuralGuy]

I'm sure there is but more information would need to be provided for a more complete answer. It would almost certainly involve VBA code and string searches.

[evilleve]

I'm sure there is but more information would need to be provided for a more complete answer. It would almost certainly involve VBA code and string searches.

Thank you for answering me

i try to use split function , but i'm newbie in vba
i want to know :
Is it possible to use the Split Function in a query?
and how i can separate a string like below , using split function ?

my field name is Welders
it contain strings like this :
(15-SW-001[SMAW]#0,534,1,12,80%,LOF,REP)(15-SW-001[SMAW]#0,534,1,12,80%,LOF,REP)

[RuralGuy]

The Split() function is very useful if all of your values are separated by a common separator like ";" or a space. In order to use the Split() function in a query you would need to create your own user defined function (UDF).

[evilleve]

The Split() function is very useful if all of your values are separated by a common separator like ";" or a space. If order to use the Split() function in a query you would need to create your own user defined function (UDF).

in first step seperator is "()" , then "#" , then ","
i can seperate them step by step
but my problem is creating this split() function

[RuralGuy]

You are welcome to use the split function and I'm sure that it can be applied here but it would require a function (UDF) to use it.

[evilleve]

You are welcome to use the split function and I'm sure that it can be applied here but it would require a function (UDF) to use it.

bro what did you mean from UDF ?
and how can i create it ?

[RuralGuy]

bro what did you mean from UDF ?
and how can i create it ?

Explained in posrt #4.

[evilleve]

for example i creat a function like below to separate a string field like this
aa-asda-sdhdhg
a
shjkk-whhsa
sd-dfa


Public Function mysplit1(oldfield As String)
myarray = Split(oldfield, "-")
mysplit1 = myarray(0)
End Function

Public Function mysplit2(oldfield As String)
myarray = Split(oldfield, "-")
mysplit2 = myarray(1)
End Function

Public Function mysplit3(oldfield As String)
myarray = Split(oldfield, "-")
mysplit3 = myarray(2)
End Function

[RuralGuy]

I would suggest something like:

Public Function MySplit(InString As String, MyDelim As String, InOffset As Integer) As String
Dim MyArray() As String
   MyArray = Split(InString, MyDelim)
   MySplit= MyArray(InOffset)
End Function

[evilleve]

I would suggest something like:

Public Function MySplit(InString As String, MyDelim As String, InOffset As Integer) As String
Dim MyArray() As String
   MyArray = Split(InString, MyDelim)
   MySplit= MyArray(InOffset)
End Function

bro , can you explain for me how it works ?

[RuralGuy]

Only if you stop calling me "bro". Have you figured out how to invoke it yet? Have you put it in a Standard Module yet?

[evilleve]

Only if you stop calling me "bro". Have you figured out how to invoke it yet? Have you put it in a Standard Module yet?

ok

for field [welders] like this :
(15-SW-040[SMAW]#0~0,LF,REP)(15-SW-041[SMAW]#0~10,LF)
(15-SW-094[SMAW]#150~151,LF,REP)
.
.
.

i put this in a module :

Public Function MySplit1(welders As String, MyDelim As String, InOffset As Integer) As String
Dim MyArray() As String
MyDelim = "("
MyArray = mySplit1(welders, MyDelim)
InOffset = 1
MySplit1 = MyArray(InOffset)
End Function

Public Function MySplit2(welders As String, MyDelim As String, InOffset As Integer) As String
Dim MyArray() As String
MyDelim = "("
MyArray = mySplit2(welders, MyDelim)
InOffset = 2
MySplit2 = MyArray(InOffset)
End Function

i made 2 function , because i need 2 field with these names : welder1 , welder2
in query i put these : welder1: mysplit1([WELDERS];"(";0)
welder2: mysplit2([WELDERS];"(";1)

in windows xp i got an error : argument not optional

in windows 7 it work good until the function found "(" , when the function cant find "(" an error happens

[RuralGuy]

Are you showing two or three records? You do realize you have no consistant delimiter to use, right?

[RuralGuy]

To be honest, this is not a good situation to use the Split() function because you do not have delimited data. As I said in my first post, you will probably ned to use string functions to get the values you need. Mid(), Left(), Right(), InStr().