Extracting a number from record for a new field

tcarter963

New Member
Joined
Aug 3, 2006
Messages
38
I have a field called SampleName for which most of the records look something like this 00468-003-03 2 Days

I would like the extract the 2 from this field and put it in a new field called Days.

Using Access 2003 how would I go about doing something like that, or is it possible?

Thanks
 

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
65,576
Office Version
  1. 365
Platform
  1. Windows
Assuming that it always follow the same format, like this:
text space number space text
to extract that number, use this code (where MyField is the name of your field):
Code:
NumDays: Mid[MyField],InStr([MyField]," ")+1,InStrRev([MyField]," ")-InStr([MyField]," ")-1)+0
 

tcarter963

New Member
Joined
Aug 3, 2006
Messages
38
Thanks a ton Joe.

I really hate to ask such a basic question, but I'm not as familiar with access as I am excel. Does this code go in Modules?

I'm mainly using this one table to pass data to excel, the trouble is getting in the right order. Using the day value in the sample name is much better than going off when the data was dumped into the database.

Yes, the format should always be text space number space text.

I created a field named NumDays and the field SampleName contains the records so the code should be as follows

NumDays: Mid[SampleName],InStr([SampleName]," ")+1,InStrRev([SampleName]," ")-InStr([SampleName]," ")-1)+0


Again, Thank you.
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
65,576
Office Version
  1. 365
Platform
  1. Windows
I really hate to ask such a basic question, but I'm not as familiar with access as I am excel. Does this code go in Modules?
No, what I gave you is just a calculated field that you would put in a query (using your table as its source).
 

Forum statistics

Threads
1,176,718
Messages
5,904,609
Members
435,101
Latest member
oldmaninla

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
Top