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

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
57,740
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
57,740
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).
 

Watch MrExcel Video

Forum statistics

Threads
1,133,639
Messages
5,660,020
Members
418,542
Latest member
Sandy3503

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