How to Move Text After the Final Space to Another Field in Microsoft Access
Question details
Extract the final word from a text string (after the last space) in one field and move it into a separate field.
- Product
- Microsoft Access
- Device & OS
- not provided
- Scenario
- Reorganizing database tables where users need to separate the last word of a string into a dedicated column.
- Observed behavior
- The goal is to automatically update the table so the original field retains everything before the last space, and the new field contains only the word after the final space.
Always create a backup copy of your database table before running an Update Query, as these queries permanently modify the existing data and cannot be undone.
Use an Update Query with InStrRev, Left, and Mid
This method is recommended because it processes the entire table automatically using standard SQL functions built into Access without requiring programming knowledge.
The InStrRev function searches for a space from the end of the string backward, allowing the Left and Mid functions to accurately split the string exactly at the last space.
Open your Microsoft Access database, navigate to the 'Create' tab on the ribbon, and click on 'Query Design'.
Close the 'Show Table' dialog box without adding any tables. Right-click the query tab and select 'SQL View' from the context menu.
Paste the following update query: UPDATE Table1 SET Pink = IIf(InStrRev([Pink]," ")>0,Left([Pink],InStrRev([Pink]," ")-1),[Pink]), Purple = IIf(InStrRev([Pink]," ")>0,Mid([Pink],InStrRev([Pink]," ")+1),Null) WHERE Purple Is Null;
Replace 'Table1' with your actual table name. Replace 'Pink' with the name of the original text field, and 'Purple' with the name of the destination field.
Go to the 'Query Design' tab on the ribbon and click the red exclamation mark icon ('Run') to execute the update. Click 'Yes' when prompted to confirm the row updates.
Use a VBA Script with the Split Function
If you are incorporating this data split into a larger macro or prefer using arrays, VBA's Split function provides an alternative way to isolate the final word.
Looking for a Free Office Suite? Try WPS Office
While Microsoft Access handles complex relational databases, many data tracking and text-splitting tasks are much easier in a spreadsheet environment. WPS Office provides a free, lightweight, and highly compatible alternative to Microsoft Office, featuring robust spreadsheet capabilities for all your data management needs.
- 1. Download and Install: Visit the official WPS Office website to download and install the free suite on your device.
- 2. Open Your Data: Export your Access data to an Excel file and open it seamlessly in WPS Spreadsheet.
- 3. Split Text Easily: Use the built-in 'Text to Columns' feature under the Data tab to split text visually without writing code.
Frequently Asked Questions
What does the InStrRev function do in Microsoft Access?
InStrRev stands for 'In String Reverse'. It searches for a specified substring (like a space) within a larger string, starting from the end of the string and moving backward, and returns its numerical position.
Can I split the text using a comma instead of a space?
Yes. Simply replace the space character " " in the InStrRev function with a comma "," within your Update Query's SQL code.
How do I undo an Update Query if I make a mistake?
You cannot undo an Update Query in Access once it has been executed. This is why it is highly recommended to copy and back up your table before running any action queries.
Why shouldn't I use the VBA Replace function for the original field?
If you use Replace(Pink, Purple, ""), it will remove every instance of the final word from the entire string. If the last word also appeared earlier in the text, it would be unintentionally deleted there as well.
