Pull a date from a cell containing text

hallingh

Well-known Member
Joined
Sep 11, 2010
Messages
769
Hi everyone,

I have a worksheet that I'd like to dynamically enter a date into a cell based on the value of another cell.. I would typically just use =LEFT(), but I'm not sure how to do this because it's a text value. I'm already using VBA to submit data to the correct sheets, so I'm guessing that's the easiest way to go.. Here's the necessary info:

Data Input Cell R1 contains the text, always in the form of "3/10/2014 - 3/16/2014"

I'd like the VBA code to input the date (in this case) 3/10/2014 into cell R3 of the same worksheet. I'd assume the best way to do it would be with some variation of the =Date() vba command, but I'm not even really sure where to start with the rest of the code..

I'td be incredibly helpful if someone could point me in the right direction!

Cheers
Hank
 

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)
=LEFT() would work fine,

=LEFT(R1,FIND(" ",R1)-1)

In vba there are a few ways do achieve the same result, for example, you could simply evaluate the formula.

Code:
Range("R3").Value = [=LEFT(R1,FIND(" ",R1)-1)]
 
Upvote 0
For reference, if it didn't, you could always use

=LEFT(R1,FIND(" ",R1)-1)+0

Which would convert a number stored as text to a proper number, although you might find that cells formatted as "general" return the date serial number instead of the actual date.
 
Upvote 0

Forum statistics

Threads
1,213,494
Messages
6,113,972
Members
448,537
Latest member
Et_Cetera

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