Selecting only certain characters from a cell value?
Hi all, In a cell value (loaded from a .cap file) i have the date, which need to use for ensuring that (when i work out how to do it), my copie range goes into the right sheet. So, i have the date in 1 cell in this format: Wed 17 Nov 2004 05:34P Is there any VBA that could almost take apart the value, and extrac the necessary data? I was thinking something along the lines of removing the first characters, keeping the next 11 and removing anything after that. That would then leave me with 17 Nov 2004, which i would then use fo the lookup fun thats ahead of me. Any ideas, or advice? Thank you so muc -- druce ----------------------------------------------------------------------- drucey's Profile: http://www.excelforum.com/member.php...fo&userid=3255 View this thread: http://www.excelforum.com/showthread.php?threadid=53450 |
Selecting only certain characters from a cell value?
=INT(A2) and format as a date
-- HTH Bob Phillips (remove nothere from email address if mailing direct) "drucey" wrote in message ... Hi all, In a cell value (loaded from a .cap file) i have the date, which i need to use for ensuring that (when i work out how to do it), my copied range goes into the right sheet. So, i have the date in 1 cell in this format: Wed 17 Nov 2004 05:34P Is there any VBA that could almost take apart the value, and extract the necessary data? I was thinking something along the lines of removing the first 4 characters, keeping the next 11 and removing anything after that. That would then leave me with 17 Nov 2004, which i would then use for the lookup fun thats ahead of me. Any ideas, or advice? Thank you so much -- drucey ------------------------------------------------------------------------ drucey's Profile: http://www.excelforum.com/member.php...o&userid=32553 View this thread: http://www.excelforum.com/showthread...hreadid=534501 |
Selecting only certain characters from a cell value?
Can you use =MID(A1,5,11) in excel? -- Jimbo1 ------------------------------------------------------------------------ Jimbo1's Profile: http://www.excelforum.com/member.php...o&userid=30637 View this thread: http://www.excelforum.com/showthread...hreadid=534501 |
Selecting only certain characters from a cell value?
Hi,
use this formula in VBA: date=cdate(mid("Wed 17 Nov 2004 05:34P ",4,len("Wed 17 Nov 2004 05:34P ")-4)) You will get date as result. In case you want to convert it somehow else, explore type conversion functions in VBA help. Regards, Ivan |
Selecting only certain characters from a cell value?
with
a="Wed 17 Nov 2004 05:34P" try format(mid(a,instr(a," ")),"dd mmm yyyy") returns 17 Nov 2004 "drucey" wrote: Hi all, In a cell value (loaded from a .cap file) i have the date, which i need to use for ensuring that (when i work out how to do it), my copied range goes into the right sheet. So, i have the date in 1 cell in this format: Wed 17 Nov 2004 05:34P Is there any VBA that could almost take apart the value, and extract the necessary data? I was thinking something along the lines of removing the first 4 characters, keeping the next 11 and removing anything after that. That would then leave me with 17 Nov 2004, which i would then use for the lookup fun thats ahead of me. Any ideas, or advice? Thank you so much -- drucey ------------------------------------------------------------------------ drucey's Profile: http://www.excelforum.com/member.php...o&userid=32553 View this thread: http://www.excelforum.com/showthread...hreadid=534501 |
Selecting only certain characters from a cell value?
Jimbo1 Wrote: Can you use =MID(A1,5,11) in excel? Works Perfectly! Thank you Jimbo -- drucey ------------------------------------------------------------------------ drucey's Profile: http://www.excelforum.com/member.php...o&userid=32553 View this thread: http://www.excelforum.com/showthread...hreadid=534501 |
All times are GMT +1. The time now is 04:43 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com