Hi I have this text "WSOPE3800 - Fire Alarm Checks v4 12082013" in my cell and I would like to extract only "12082013" but I would like it to be converted into a date format like 12/08/2013 is this possible and if so does anyone knows the right formula to acheive this?
Thank you
Dan
does these dates always appears at the end and 8 characters in length ?
or could you post some more examples ?
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
WANT TO SAY THANKS, HIT ADD REPUTATION (*) AT THE BOTTOM LEFT CORNER OF THE POST
More we learn about excel, more it shows us, how less we know about it.
for chemistry
https://www.youtube.com/c/chemistrybyshivaansh
HiOriginally Posted by hemesh
Yes they should always appear at the end :-)
Thank you
Dan
assuming your data is in a1 then in b1 try below
=DATEVALUE(LEFT(RIGHT(A1,8),2)&"/"&MID(RIGHT(A1,8),3,2)&"/"&RIGHT(RIGHT(A1,8),4)) then format column as date and will be treated as actual dates
if you just want it to look alike dates then use
=LEFT(RIGHT(A1,8),2)&"/"&MID(RIGHT(A1,8),3,2)&"/"&RIGHT(RIGHT(A1,8),4)
=RIGHT(A2,2)&"/"&LEFT(RIGHT(A2,4),2)&"/"&Left(A2,4)
Notice my main language is not English.
I appreciate it, if you reply on my solution.
If you are satisfied with the solution, please mark the question solved.
You can add reputation by clicking on the star * add reputation.
Thank you all for your help :-)
Dan
This works for me...
=--TEXT(RIGHT(A2,8),"0-00-0000")
Format as Date
If you're using the date format dd/m/yyyy you might have to swap some of the zeros around:
=--TEXT(RIGHT(A2,8),"00-0-0000")
Biff
Microsoft MVP Excel
Keep It Simple Stupid
Let's Go Pens. We Want The Cup.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks