Get specific words/letters in a field and put in another field

dhen21dx

Board Regular
Joined
May 10, 2013
Messages
166
Hi

Can anyone know here how i will get some words or letter in the fields of the table and put it in another field.
Thanks
Here is the example:

tblfromemail:

Field Name: Subject
Ticket Number: Num '7003894' Implement Task. Due Date: '2017-10-16 15:40:42'.Changes are released

Result:
tblfromemail:

Field Name: Ticket Num
7003894
Field Name: Due Date
2017-10-16 15:40:42
 

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!
create a module , then paste this code into it.
run a query to get the data out of the field:
usage:
select getTicket([subject]) as Ticket ,getDueDate([subject]) as DueDate from table

Code:
'------------
function getTicket(pvFld)
'------------
dim i as integer
dim vWord

i = instr(pvfld,"'")
vWord = mid(pvFld,i+1)
i = instr(pvfld,"'")
getTicket = left(vWord,i-1)
end function


'------------
function getDueDate(pvFld)
'------------
dim i as integer

i = instr(pvfld,"Due Date:")
getDueDate= mid(pvFld,i+11,19)
end function
 
Last edited:
Upvote 0
Hi Ranman,

Thank you, but have some minor problem i think on Due Date because of the format of Date is 'yyyy-m-d hh:nn:ss', so some month and date vary. example is below:

'2017-8-1 05:16:08'
'2017-7-31 05:43:20'
'2017-10-11 03:39:21'
 
Upvote 0
What is the Data Type of the table field that you are trying to post this to?
How exactly are you trying to insert that value into that field?
 
Upvote 0
Hi Joe,

The data type is Date/Time field, i am inserting it using the function
'------------
function getDueDate(pvFld)
'------------
dim i as integer

i = instr(pvfld,"Due Date:")
getDueDate= mid(pvFld,i+11,19)
end function
 
Upvote 0
The data type is Date/Time field, i am inserting it using the function
'------------
function getDueDate(pvFld)
'------------
dim i as integer

i = instr(pvfld,"Due Date:")
getDueDate= mid(pvFld,i+11,19)
end function
That is just the function. That doesn't insert it into any table, it just returns a value.
You need to use it in something like an Update Query or VBA code to actually insert it into a table field.
 
Upvote 0
I use this

select getTicket([subject]) as Ticket ,getDueDate([subject]) as DueDate from tblfromemail
 
Upvote 0
I use this

select getTicket([subject]) as Ticket ,getDueDate([subject]) as DueDate from tblfromemail
That is just a select query returning calculating fields.
Once again, that is not inserting any values into any table fields. It is just returning calculations.
You would need to use an Update Query to update field values.

See: http://www.databasedev.co.uk/update_query.html
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,256
Members
448,557
Latest member
richa mishra

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top