Split text into a new column (1 Viewer)

net

Registered User.
Local time
Today, 08:55
Joined
Mar 12, 2006
Messages
50
Hello Forum Experts,

Is it possible to perform a function in Access 2010 like the "Text to Columns" function in Excel?

I would like to split concatenated text in one column and place into a new column.

Example: 5551111-Blue- Play

I would like to split the "5551111" number and place it in its own cell.

Any suggestions? :)
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 08:55
Joined
Aug 30, 2003
Messages
35,743
If the length is consistent, you can just use the Left() function. If it's not but the dash is, you can use the Left() function in conjunction with the InStr() function to find the position of the dash.
 

sxschech

Registered User.
Local time
Today, 08:55
Joined
Mar 2, 2010
Messages
754
You can also try the split function. Paste the following code into a new module.

For the module:
Code:
Function QuerySplit(FieldName As String, Delim As String, Position As Integer)
    QuerySplit = Split(FieldName, Delim)(Position)
End Function
For below, change all the "My's" to the actual name of the fields and table/query you are using.

Then in your query put in this statement. The following is based assuming that the delimiter is the dash "-". Split function is zero based so if you want to grab the first item, it would be 0.

If using design view...
For the query:
MySplitField:Trim(querysplit([myfield],"-",0))
This is how it would look in sql view rather than design view...
A sample sql:
Code:
SELECT myfield, Trim(querysplit([myfield],"-",0)) AS MySplitField
FROM mytable;
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 10:55
Joined
Feb 28, 2001
Messages
23,163
Let's be a bit more clear.

In Excel you can dynamically split columns and create a new column more or less at will. Excel is not that picky about keeping columns pristine and pure.

In Access, those columns are hard-designed to a fixed layout. They are not nearly so dynamic. If you had a place in your table for the three components, then you could use the SPLIT function in VBA to extract the sub-fields and place them in the table's extant fields. But you would probably break a lot of stuff by trying to dynamically add columns where they didn't exist before this situation arose.
 

Users who are viewing this thread

Top Bottom