Help with Extracting Data Text to Column

[net]

Hello,

I need help with extracting a word from one column and placing it into another column, like the “Text to Columns” wizard in Excel 2016.

Example:

Using a Microsoft query, I would Like to extract the word “Completed” from Column A and place it in Column B

WO 4452200 -COMPLETED – WO
WO 7774550 -COMPLETED - WO
WO 2269003 -COMPLETED - WO

I am using Microsoft Access 2016.

Appreciate the help.

 

[isladogs]

Not sure what you mean by 'column' in an Access database
Excel has columns. Access has fields

Assuming your query field containing the string is called MyField then add another field as follows:

Mid(MyField,InStr(MyField,"-")+1,InStrRev(MyField,"-")-3)

NOTE: this assumes the hyphens before / after the word COMPLETED are ALWAYS arranged as shown in your post
i.e. no space in front , single space after

 

[arnelgp]

you need to create an Update Query
to update ColumnA (remove the "COMPLETED" word),
and ColumnB (put the "COMPLETED" word), on
records with "COMPLETED" on them:

UPDATE TableTextToColumn 
SET TableTextToColumn.ColumnA = Trim(Replace(Left([ColumnA],InStr([ColumnA],"COMPLETED")-1),"-","")), TableTextToColumn.ColumnB = "COMPLETED"
WHERE (((InStr([TableTextToColumn].[ColumnA],"COMPLETED"))<>0));

remember to replace TableTextToColumn table name
with the correct name of your table.
also ColumnA and ColumnB with correct name
of your field in the table.