How to Split One Column Into Two in Access Using the Split Function

In this video we're going to learn how to split one column into two by using the Split function in Microsoft Access and VBA. As long as there is a definite pattern in the source column, it is easy to use the split pattern to separate elements into their own columns. This can be used with any delimiters; for example , ~ | or even multi-character delimiters like ", " or a word with spaces " and " etc. Because you're using a custom function, it gives powerful results because you can add extensive programming to handle exceptions, muddy data, complex transformations, and more. I use this one all the time in transformations. It can also be used in vb.Net for batch transformations etc.

Use it to just split one column into two, or walk through a 50 column CSV file and split up data row by row for intricate transformations. The possibilities are endless.

Related Videos:
How to Use Union Queries in MS Access
How to Split One Column Into Two in Access Using the Split Function
You are watching this video now!
How to Query the Last Row in a Series in Microsoft Access
How to Format Dates in MS Access Queries, Forms, and VBA Code
How to Use Nz in Microsoft Access to Handle Null and Empty Values
How to Use Crosstab Queries in MS Access
How to Use Functions in MS Access Queries

Follow us on social media:

Get Microsoft Office including Access:

Got a YouTube Channel? I use TubeBuddy, it is awesome. Give it a try:

how to split one column into two in access using the split function
split one column into two in access
access vba split function
vba split function
vba split function example
how to split a delimited string in access
data analytics
data analysis
data transformation
data science
microsoft access
split function
visual basic for applications
sean mackenzie

[seanmackenziedataengineering]

*** HOW TO CHANGE FUNCTION FOR 3 OR MORE COLUMNS ***

Function GetColumn(strProductCode As String, strColumn As String) As String
'Gets one of three or more columns
Dim arCode 'variant will convert to array
Dim strReturn As String
strReturn = ""
On Error GoTo Sub_Exit
arCode = Split(strProductCode, "-")
Select Case strColumn
Case "Column1"
strReturn = arCode(0)
Case "Column2"
strReturn = arCode(1)
Case "Column3"
strReturn = arCode(2)
'Case "Column4"
'strReturn = arCode(3)
Case Else
strReturn = "N/A"
End Select
Sub_Exit:
GetColumn = strReturn
End Function

You can uncomment the lines for the 4 column case if you need 4 columns, and add more cases if you need it!

[grayquiksilver]

Very helpful overview. Thanks for this Sean!

[WeirdestRocket]

Great Video! Thank you so much. Just one quick question. How do I get it to work to split it to 3 columns?

Ex. I have a cell that has AA-BA-19
I want these to be separated into 3 columns.

What do I need to add to the code?

[rizbfatango]

Thank you.. Great video. My question: I extract data daily from a secured website into Access DB. I update (Save) the new data on the same previous file by using 'save as' and saving it using the same name of the previous file. If I added this function to the previous data set . Does the function work Dynamically on the newly extracted data?

[solomystery]

Thank you for the informative video. However, I have a question about this. What should I do if I have a field with varying numbers of commas? How can I convert one row into 2 columns and the other into 5 and the other into 3, based on the number of commas. Inputs would be much appreciated. And also is it possible to split on the basis of 2 different special characters like "/" and ", " ?

[ericcohen]

Hi Sean, this video was very helpful and worked well where the base data had three "-", however I have some records that are populated with "Unclassified" and are causing a Runtime Error 9: Subscript Out of Range. I would have thought the Case Else N/A would have solved for this. Any ideas? Thanks in advance...

[ApexRailAutomation]

Can this be used to separate into 3 columns when there are 2 different separators? For Example GRASS VALLEY-G2:4C2, I would need to separate useing " - " and " : ". Thank you

[ericmild]

ya saved my bacon! thanks for that. another subscriber for you!

[cjparrott]

a bit a bass-ackwards way to do but, what i did was do a "make table" query and include only the ID column and the column i want to split.
i then exported the 2 column "made table" as a text file and adjusted the delimited export option to 'space' and text qualifier 'none'. (yours might be different)

i then re-imported the text file and again selected the delimited character 'space' and text qualifier 'none' to create a new table that has split columns. 2 split to 3

in my case i only wanted to split one column. the column was 'text'. i wanted to split it and then have the number portion formated as a 'number'
my example was a text column for Elevation where the entry was, for example, "21.1 m." i want to do queries/thematic maps/etc that are number based and need this column to be a number. so i had to figure a way to split and re-format. (who ever made this elevation table for topography must not be a data nerd)

once i had my new 3 column table with elevation split into a number and text I,

1. (EDIT: - THIS DIDN'T WORK FOR KEEPING THE gEOMETRY ATTRIBUTES:) did another "make table" query joining by ID with the oriniigal table and made a new table with split columns (and the one now formatted as a number).)

2. added a new column to my orinigal table, then joined them in an UPDATE query and put this [source_table].[source_field] into the 'update' field. THIS WORKED!!! woooohooo. JUST ADDED A NEW COLUMN TO THE ORIGINAL THAT DIDN'T AFFECT THE GOEMETRY, MEANING I COULD NOW MAP BASED ON THE ELEVATION.

great video, but the VBA is beyond me.... i have zero expereince with VBA... it scares me

thanks for making these vids

[ambrandaoyoutub]

What if we don't know exactly the number of words that compose the field that we want to separate? And after, put it as new row with the same ID Key field, adding another field with the number of the position of the words? Table fields: "Id key", "word number" of the split field and each "Word".
Or
Put the array as new rows with the same "ID key" and adding another field with the "word number" position of each word and the separate "word" it self.

[fredericcousin]

hi i try to make a similar exemple (table modulke query very close) but when i launch the query i have an error like execute error 9 the indice do not be to the selection (sorry for my english french) :) )

[pilotgarry]

I tried that first but get a message saying that the subscript is out of range, the line strReturn = arCode(2) is highlighted in yellow which I think means that I am out of the array

[pilotgarry]

Great video, worked well splitting into two fields but I can’t seem to get it to work for 3

[pilotgarry]

On inspection the problem does lie with the data. I imported the data from an excel spreadsheet, and you know how unreliable they can be