Extract a specific data from a cell
Solved/Closed
Related:
- Extract a specific data from a cell
- Transfer data from one excel worksheet to another automatically - Guide
- Tmobile data check - Guide
- Extract audio from video vlc - Guide
- Count if cell contains number - Excel Forum
- How to reset safe folder password without losing data ✓ - Android Forum
8 responses
rizvisa1
Posts
4478
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
766
Feb 3, 2010 at 09:20 AM
Feb 3, 2010 at 09:20 AM
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
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
rizvisa1
Posts
4478
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
766
Feb 3, 2010 at 09:58 AM
Feb 3, 2010 at 09:58 AM
Sorry forgot that part
try this
=MID(A1,FIND(":",A1,1) + 6, 7) & MID(A1, LEN(A1)-4,4)
try this
=MID(A1,FIND(":",A1,1) + 6, 7) & MID(A1, LEN(A1)-4,4)
rizvisa1
Posts
4478
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
766
Feb 3, 2010 at 10:41 AM
Feb 3, 2010 at 10:41 AM
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)
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
"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.
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
Posts
4478
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
766
Feb 4, 2010 at 11:44 AM
Feb 4, 2010 at 11:44 AM
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
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
Didn't find the answer you are looking for?
Ask a question
rizvisa1
Posts
4478
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
766
Feb 4, 2010 at 06:10 AM
Feb 4, 2010 at 06:10 AM
"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
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
rizvisa1
Posts
4478
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
766
Feb 4, 2010 at 07:25 AM
Feb 4, 2010 at 07:25 AM
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 https://authentification.site as I have been told to ask instead of getting it in email.
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 https://authentification.site 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!
https://authentification.site/files/20750423/Sample_file_04-02-10_Rev1.0.xls
Thanks again
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!
https://authentification.site/files/20750423/Sample_file_04-02-10_Rev1.0.xls
Thanks again
rizvisa1
Posts
4478
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
766
Feb 3, 2010 at 05:20 AM
Feb 3, 2010 at 05:20 AM
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
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
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
Posts
4478
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
766
Feb 4, 2010 at 07:07 AM
Feb 4, 2010 at 07:07 AM
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
Feb 3, 2010 at 09:44 AM