Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 Other Forums
 MS Access
 text to coloum command

Author  Topic 

YangAhBang
Starting Member

3 Posts

Posted - 2007-04-15 : 08:54:54
hi everyone.

i need help.

this is the senario
we have 2 fields name, A & B
Field A Field B
Apple ABC.111
Carrot DE.3.2.0
i want to is to split field B, the separator is the '.' then merge it with field A hence getting the result below

Field C
AppleABC
CarrotDE

currently i do them all in Ms Excel by first do a "text to coloum" for field B then delete all fields after the '.' then do a "concatenate command" to merge field A to the new field.

can anyone help me here.. i'm not using SQL just normal query.


any help is much appreciate. and much thks in advance.

Guus2005
Starting Member

10 Posts

Posted - 2007-05-08 : 05:49:45
Use my recursive GetPart() function

Public Function GetPart(strString As String, strSep As String, intPart As Integer) As String
Dim intFound As Integer
Dim intNext As Integer

intFound = InStr(1, strString, strSep)
If intFound > 0 Then
If intPart = 1 Then
GetPart = Mid$(strString, 1, intFound - 1)
Else 'intPart > 1
GetPart = GetPart(Mid$(strString, intFound + 1), strSep, intPart - 1) 'recursive
End If
Else 'intFound = 0, no occurence of seperator so return complete string
GetPart = strString
End If

End Function


?GetPart("DE.3.2.0", ".", 2)
3

Enjoy!
Go to Top of Page
   

- Advertisement -