Split text into a new column

[net]

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]

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]

You can also try the split function. Paste the following code into a new module.

For the module:

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:

SELECT myfield, Trim(querysplit([myfield],"-",0)) AS MySplitField
FROM mytable;

 

[The_Doc_Man]

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.