Splitting Data

[ErnDog]

I have a table that has a column listing product description. This column
contains a special code among the other details. I need to separate this
code from the rest of the data that is held in the same cell. An example
would look like:
9753POLASERONEOPAQUEMF1385
I need to extract the MF1385 from this line and place it into its own
cell/column.
Can this be done somehow? It's like the opposite of concatenation
Thanks

 

[Douglas J. Steele]

Is it always 6 characters? You can use the Right function to extract it.

If it's not always 6 characters, how can you identify what it is you need to
extract?

 

[Arvin Meyer [MVP]]

It's called parsing

Create a new column in your table then run a query like:

Update MyTable Set NewColumn = Right([OldColumn],6);

First run it as a Select query to make sure it's giving you exactly what
you're looking for.

 

[ErnDog]

The cells always end in that format; "MF" followed by 4 digits. What is the
Right function?

 

[Douglas J. Steele]

It's function that returns a specified number of characters from the right
end of a string.

This is from Visual Studio, but it's the same function:
http://msdn2.microsoft.com/en-us/library/dxs6hz0a(VS.80).aspx

 

[ErnDog]

I understand your thinking but where do I enter the right command when
working in the query?

It's called parsing

Create a new column in your table then run a query like:

Update MyTable Set NewColumn = Right([OldColumn],6);

First run it as a Select query to make sure it's giving you exactly what
you're looking for.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

I have a table that has a column listing product description. This column
contains a special code among the other details. I need to separate this
code from the rest of the data that is held in the same cell. An example
would look like:
9753POLASERONEOPAQUEMF1385
I need to extract the MF1385 from this line and place it into its own
cell/column.
Can this be done somehow? It's like the opposite of concatenation
Thanks

 

[Arvin Meyer [MVP]]

Right() is a built in VBA string function. In this case, I'd enter the line
of SQL code directly in an SQL window. (on the menu, View >>> SQL). You can,
using the QBE query grid, also create a query with an alias column, then use
that column in an update query. That would look like:

Expr1: Right([OldFieldName],6)
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

I understand your thinking but where do I enter the right command when
working in the query?

It's called parsing

Create a new column in your table then run a query like:

Update MyTable Set NewColumn = Right([OldColumn],6);

First run it as a Select query to make sure it's giving you exactly what
you're looking for.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

I have a table that has a column listing product description. This
column
contains a special code among the other details. I need to separate
this
code from the rest of the data that is held in the same cell. An
example
would look like:
9753POLASERONEOPAQUEMF1385
I need to extract the MF1385 from this line and place it into its own
cell/column.
Can this be done somehow? It's like the opposite of concatenation
Thanks

 

[Douglas J. Steele]

You can either work directly with the SQL, or, if you prefer working with
the graphical query builder, create a new query and select your table. Drag
the field where you want the new data stored into the grid. Change the query
to an Update query (you can do this through the Query menu). In the "Update"
row under the field, put Right([OldColumn], 6)

I'd recommend doing this on a copy of your table, just in case.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

I understand your thinking but where do I enter the right command when
working in the query?

It's called parsing

Create a new column in your table then run a query like:

Update MyTable Set NewColumn = Right([OldColumn],6);

First run it as a Select query to make sure it's giving you exactly what
you're looking for.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

I have a table that has a column listing product description. This
column
contains a special code among the other details. I need to separate
this
code from the rest of the data that is held in the same cell. An
example
would look like:
9753POLASERONEOPAQUEMF1385
I need to extract the MF1385 from this line and place it into its own
cell/column.
Can this be done somehow? It's like the opposite of concatenation
Thanks

 

[Arvin Meyer [MVP]]

I'd recommend doing this on a copy of your table, just in case.

Even an experienced professional should follow that advice.