How to Split One Column Into Two in Access Using the Split Function
HTML-код
- Опубликовано: 1 авг 2024
- 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 Use Union Queri...
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 Query the Last ...
How to Format Dates in MS Access Queries, Forms, and VBA Code
• How to Format Dates in...
How to Use Nz in Microsoft Access to Handle Null and Empty Values
• How to Use Nz in Micro...
How to Use Crosstab Queries in MS Access
• How to Use Crosstab Qu...
How to Use Functions in MS Access Queries
• How to Use Functions i...
Follow us on social media:
/ mackenziedataanalytics
/ seamacke
/ seamacke
/ seamacke
/ psmackenzie
Get Microsoft Office including Access:
click.linksynergy.com/fs-bin/...
Got a RUclips Channel? I use TubeBuddy, it is awesome. Give it a try:
www.tubebuddy.com/seanmackenz...
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 
Наука
*** 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!
Hello Sean,
I'd like to first say thank you for sharing your knowledge. This is exactly what I needed.
I have 12 columns that need to be split and each column will create 13 more columns after using the above code.
I am having great success with splitting one column with the above code and got my output with 13 different columns.
However, I still have 11 more columns. What is the most efficient way to split all 12 columns from one code?
My background- Novice in VBA, started learning from your video.
Any help would be greatly appreciated. Thank you.
Very helpful overview. Thanks for this Sean!
Glad it was helpful!
Amazing!! Thank you for this video!
You're so welcome!
a bit a bass-ackwards way to do it....... 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 waaaaay beyond me.... i have zero expereince with VBA... it scares me
thanks for making these vids
Hey that's a pretty cool workaround! Thanks for sharing that one. Pretty creative with the Make Tables etc. Cheers
ya saved my bacon! thanks for that. another subscriber for you!
Glad I could help! Thanks for the sub!
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?
Hi Kazuhiko, please see the comment I just pinned for your solution!
Great video, worked well splitting into two fields but I can’t seem to get it to work for 3
Thanks for the question! On a new line before Case Else, put
Case "NewField"
strReturn = arCode(2)
That will allow you to ask for GetModelOrNumber([Product Code], "NewField") in your query for the third split field.
Side note: The function name GetModelOrNumber could easily be called GetFieldValue and strModelOrNumber could be called just strField, to account for many fields. I used ModelOrNumber just to show the meaning of Model or Number.
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 "," ?
Great question! If you have a field with a varying number of commas, then you need to do some analysis to see that the data is at least in order, and only trailing commas are missing. Then you could make a function to calculate the maximum columns in any split output column. After that, you could modify the split procedure to account for trailing entries that don't exist.
For example, if you discovered that the field contained data for: animal, color, food, toy
And delimited data had field values:
dog, brown
cat
dog, black, kibble
cat, orange, fish treats, string toy
Then, this approach would work. You could discover the number of columns, then alter the procedure to accommodate the missing entries.
Regarding your question on delimiter type, the answer is yes, you can choose between "/" and "," by specifying this in the arguments for the Split function.
Thanks for an interesting question!
thanks
No problem
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?
Yes, it will work on the newly extracted data. The moment you open the query, it will split the columns.
@@seanmackenziedataengineering Thank you Sean.That is very helpful.
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
Yes, you can just put 2 steps in your function. The first step will split by "-" and the second will split by ":". So, you'll use split twice!
arItems = Split(strItems, "-")
strFirst = arItems(0)
strPart2 = arItems(1)
arItems = Split(strPart2, ":")
strSecond = arItems(0)
strThird = arItems(1)
Good luck!
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...
Hi Eric, on a new line after:
strReturn = ""
just put:
On Error Resume Next
and it will just return an empty column for the times when there is no "-" in the column. Let me know how it goes!
Thanks Sean. I followed your suggestion and it put the Unclassified in the first field which is fine but errored on the other 3 columns. Should I insert the On Error Resume Next after each Case?
I see. Try this instead:
Change On Error Resume Next to On Error GoTo Sub_Exit
then, on a new line just before
GetModelOrNumber = strReturn
put the line
Sub_Exit:
That will tell it to jump to the end if there is an error, and you have an empty string in strReturn, so it should work.
If that doesn't work, post your code (or PM if you prefer), and I'll take a look!
It worked! Thanks again for your help, Sean.
Awesome! No problem - good luck with your project.
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) :) )
This probably means that one of the fields in your input column does not have two values. For example, "model1-1234" is ok, "model2-3333" is ok, "model3" is not ok; it is missing "-567". You must have two elements in each entry. Check your data to see if it is all OK.
However, you can modify this code to ignore the problem for more advanced cases. Perhaps this would be a good idea for an upcoming video!
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
What is your input string? In this example you must have 3 items in it. For example product111-model123-newfield555
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.
Great question! The array will automatically create the correct number of elements when you use the Split function. By using UBound, you can find out the number of "word" elements. Since the number of fields would be variable, using a static query like we show in the video would not work; however, assuming the maximum number of words is not huge, you could cycle the set to find the maximum number of words, create a table with that number of fields (plus the ID), then write the data to the table.
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
Ok, I'm glad you figured out that one! I definitely understand about the spreadsheet!