Extract a specific data from a cell [Solved/Closed]

picachoo - Feb 3, 2010 at 04:34 AM - Latest reply:  picachoo
- Feb 5, 2010 at 03:41 AM
Hello,
I have a column of data in text format in excel, that has a persons name, date and time. I am only interested in one person's name, date and time. How can I extract that one person with date and time and then convert that to just date leaving out the person's name? Please help.
Sample cell info below: lets say I am only interested in Name3 and date and time
Name1:"Tue Sep 29 15:10:33 2009",Name2:"Thu Sep 24 16:12:44 2009",Name3:"Tue Sep 29 12:49:43 2009"
See more 

17 replies

Best answer
rizvisa1 4481 Posts Thursday January 28, 2010Registration dateContributorStatus January 6, 2016 Last seen - Feb 3, 2010 at 09:20 AM
5
Thank you
Will your date be always like this

DDD MMM dd hh:mm:ss YYYY

if thats so you can use the formula as
=MID(A1,5,6) & RIGHT(A1, 5)
A1 is the cell where the date is

Thank you, rizvisa1 5

Something to say? Add comment

CCM has helped 1669 users this month

Yes thank you for replying. The cell includes the "Name3:" as well. I understand it, but can I change this format to recognize as a date format?
rizvisa1 4481 Posts Thursday January 28, 2010Registration dateContributorStatus January 6, 2016 Last seen - Feb 3, 2010 at 09:58 AM
3
Thank you
Sorry forgot that part

try this

=MID(A1,FIND(":",A1,1) + 6, 7) & MID(A1, LEN(A1)-4,4)
Yes Thank you, that part worked well. How can I turn it to a date formatted cell? at the moment it looks like a text formatted.
rizvisa1 4481 Posts Thursday January 28, 2010Registration dateContributorStatus January 6, 2016 Last seen - Feb 3, 2010 at 10:41 AM
2
Thank you
Well it is possible but that would be one long and troublesome call. You are better off using a public function to all your dirty deeds


create a public function by pressing ALT +f11, then choose insert, and then choose module and paste the code below

Public Function extractDate(sMyString As String) As Date
Dim dtMyDate As Date

If sMyString = "" Then Exit Function

sStringPart = Split(sMyString, " ")

dtMyDate = format(CDate(sStringPart(1) & " " & sStringPart(2) & ", " & Left(sStringPart(4), 4)), "yyyy-mm-dd")

extractDate = dtMyDate

End Function


then use it where you want to display the date
for example if the string was in A1 as Name3:"Tue Sep 29 12:49:43 2009"
you can put the call to function ib b2 as =extractDate(a1)
Hi, Thank you so much for helping me on this. Appologies, didn't see your reply till now. I did every thing as per your instruction, bit I don't understand the follwing
"then use it where you want to display the date
for example if the string was in A1 as Name3:"Tue Sep 29 12:49:43 2009"
you can put the call to function ib b2 as =extractDate(a1)"

put the call to function ib - can you expand on this please . sorry to be a nuisance

thanks
Hi rizvisa1,

Thank you, since replying, I tried and workd like a dream. Thank you soooooo much. Just a minor point I noticed in the following data (Name3:"Tue Sep 29 12:49:43 2009") if the date is 2 digit then it works, but when the date is a single digit eg: Name3:"Tue Sep 9 12:49:43 2009" then it is giving me an error which displays #VALUE!. It must be something in the VB-macro, but I am not good at the macro so I am not sure where it is looking for 2 digits?
Any way thank you so vey much for all the help so far.
rizvisa1 4481 Posts Thursday January 28, 2010Registration dateContributorStatus January 6, 2016 Last seen - Feb 4, 2010 at 11:44 AM
2
Thank you
Replace

dtMyDate = Format(CDate(sstringpart(1) & " " & sstringpart(2) & ", " & Left(sstringpart(4), 4)), "yyyy-mm-dd")

with

If (sstringpart(2) = "") Then
dtMyDate = Format(CDate(sstringpart(1) & " " & sstringpart(3) & ", " & Left(sstringpart(5), 4)), "yyyy-mm-dd")

Else
dtMyDate = Format(CDate(sstringpart(1) & " " & sstringpart(2) & ", " & Left(sstringpart(4), 4)), "yyyy-mm-dd")
End If
Hi rizvisa1,

Thank you. that sort the problem for me. Many thanks. 5* for your reply.

picachoo.


ps. I thought about why you don't win a lottery, mmm, may be you don't buy a ticket to give yourself a chance to win : )? good luck in the future.
rizvisa1 4481 Posts Thursday January 28, 2010Registration dateContributorStatus January 6, 2016 Last seen - Feb 4, 2010 at 06:10 AM
1
Thank you
"ib" is my fat fingers. Was supposed to say "in"


I am saying that presuming that your string
Name3:"Tue Sep 29 12:49:43 2009" is in (row 1 and column 1) A1
and you want the date to show in (row 1 and column 2) B1
then in B1 you can use the function as =extractDate(A1)
This will show the date as real excel date and you can format it to the way you want
Hi, thank you, after reading it few times I thought it might be an "in". did you see my reply regarding the 1digit date? giving me an error?
rizvisa1 4481 Posts Thursday January 28, 2010Registration dateContributorStatus January 6, 2016 Last seen - Feb 4, 2010 at 07:25 AM
1
Thank you
Lets step back for a moment.
As I understand (and trust me there is a lot that I dont understand, like why I never win a lottery but thats beside the point) So as I was saying, from what I understand,:

you were able to separate the value Name3:"Tue Sep 29 12:49:43 2009"

Now the issue was to separate out the date and to separate it out as a "real excel date"

so lets say

Step 1:
before any thing
Cell A1 looks like this (either via formula or as values does not matter)
Name3:"Tue Sep 29 12:49:43 2009"

Step 2: want to separate out date in cell B1
So in B1 you write
=extractDate(A1)

Now of course I must be missing some thing, so why dont you walk me thru or post your book at http://www.speedyshare.com as I have been told to ask instead of getting it in email.
Hi,

Ok I have uploaded the file now, only a portion of it as the file is long and we generate it daily and this will grow. This will give enough information I think!

http://www.speedyshare.com/files/20750423/Sample_file_04-02-10_Rev1.0.xls

Thanks again
rizvisa1 4481 Posts Thursday January 28, 2010Registration dateContributorStatus January 6, 2016 Last seen - Feb 3, 2010 at 05:20 AM
0
Thank you
No entirely sure about your end game, how ever you can use text to column functionality found under data. Click there and choose delimited

first convert on comma
so this
Name1:"Tue Sep 29 15:10:33 2009",Name2:"Thu Sep 24 16:12:44 2009",Name3:"Tue Sep 29 12:49:43 2009"

will split into three cells as
Name1:"Tue Sep 29 15:10:33 2009" Name2:"Thu Sep 24 16:12:44 2009" Name3:"Tue Sep 29 12:49:43 2009"


then once more split on : For this you have to use "Other" and manually type in : Also make sure that Text qualifier is choosen as "
This will result in
Name1:"Tue Sep 29 15:10:33 2009" Name2:"Thu Sep 24 16:12:44 2009" Name3:"Tue Sep 29 12:49:43 2009"

becoming
Name1 Tue Sep 29 15:10:33 2009 Name2 Thu Sep 24 16:12:44 2009 Name3 Tue Sep 29 12:49:43 2009
Thank you for your reply, I don't think I understand what every thing meant? the column will contain user response, hence each cell is have few users with respective dates. I am only interested in one user response's date. eg lets say Name3 is the user and I am interested in the date the user "Name3" responded in a date format also. Since asking the question I managed to extract user "Name3" data in one cell next to the original. Now I am stuck as how get the date out of that cell in date format?

data extracted (see original post for long string)
Name3:"Tue Sep 29 12:49:43 2009"

ideally the end results I like to see is Sep 29 2009 in a date format
rizvisa1 4481 Posts Thursday January 28, 2010Registration dateContributorStatus January 6, 2016 Last seen - Feb 4, 2010 at 07:07 AM
0
Thank you
Use the function. It should not give you that error. if you are using this =MID(A1,FIND(":",A1,1) + 6, 7) & MID(A1, LEN(A1)-4,4) , it may give you that error. The reason is that is it all dependent on assumption that different elements of date would have same length. So you are better off with the use of extractDate() as it is not dependent on any such thing
Hi,

I made the extracted data to just value, so I don't have any formula in the cell where the extractdate is looking.