ms access sql expression to seperate data

kevinh2320

Board Regular
Joined
May 13, 2016
Messages
61
I have a column in my MS ACCESS query called Field1 with data similar to what's shown below. I'm looking for SQL expressions to separate this information into 3 separate AS columns. So, looking at the top row that would be .00 then .00 and then BILLING ACTIVATED. Thanks!

Field1
.00 .00 BILLING ACTIVATED
.00 .00 BILLING ACTIVATED
.00 6,500.00 BILLING ACTIVATED
.00 .00 CLOSED
5,800.00 .00 BILLING ACTIVATED
.00 325.00 REACTIVATED
.00 .00 LEASE TERMINATED
.00 .00 RELINQUISHED
.00 .00 PENDING/SUSPEND/I
 

Some videos you may like

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

QuietRiot

Well-known Member
Joined
May 18, 2007
Messages
1,077
SELECT SplitString(column, ' ', 0), SplitString(column, ' ', 1), SplitString(column, ' ', 2)

Code:
Public Function SplitString(str As String, delimiter As String, column As Integer) As String
    Dim strArr() As String
    strArr = Split(str, delimiter)
    
    If column > 1 Then
        For i = 2 To UBound(strArr)
            SplitString = SplitString & strArr(i) & " "
        Next
        
        SplitString = Trim(SplitString)
    Else
        SplitString = strArr(column)
    End If
End Function
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,655
Office Version
  1. 365
Platform
  1. Windows
For a non-VBA solution, you could use Access Query String Functions, like this:
FirstField: Trim(Left([Field1],InStr([Field1]," ")))
SecondField: Trim(Left(Mid([Field1],Len([FirstField])+2),InStr(Mid([Field1],Len([FirstField])+2)," ")))
ThirdField: Trim(Mid([Field1],InStr(InStr([Field1]," ")+1,[Field1]," ")))


Though I like the use of a User Defined Function too (a bit cleaner)!
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,655
Office Version
  1. 365
Platform
  1. Windows
Not sure which solution you went with, but glad you got it working!
 

Watch MrExcel Video

Forum statistics

Threads
1,127,642
Messages
5,626,049
Members
416,158
Latest member
CaliburBlade138

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top