Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
convert 5 characters in a cell to 6 characters by adding a zero | Excel Discussion (Misc queries) | |||
Excel 2007 single cell selecting muliple cell | Excel Worksheet Functions | |||
Selecting a cell entry based on cell validation selection | Excel Worksheet Functions | |||
Transfer cell values to another cell by selecting button. | Excel Worksheet Functions | |||
Excel 2002: Single step of selecting characters | Excel Discussion (Misc queries) |