Extract Date from line of text

Is there any way I can extract a date from a cell containing a line o
text???

Eg:
A1: "16-02-04-Received & Completed"

How can I extract the date "16-02-04" & display it in Cell B1??

--
Message posted from http://www.ExcelForum.com

0
3/2/2004 11:41:48 AM
excel.misc 78881 articles. 5 followers. Follow

3 Replies
393 Views

Similar Articles

[PageSpeed] 42

Hi
if your date is always at the beginning of the string and always in the
format "DD-MM-YY" try the following in B1
=LEFT(A1,8)


--
Regards
Frank Kabel
Frankfurt, Germany

> Is there any way I can extract a date from a cell containing a line
of
> text???
>
> Eg:
> A1: "16-02-04-Received & Completed"
>
> How can I extract the date "16-02-04" & display it in Cell B1???
>
>
> ---
> Message posted from http://www.ExcelForum.com/

0
frank.kabel (11126)
3/2/2004 11:50:35 AM
Perfect! Thanks what works a charm

--
Message posted from http://www.ExcelForum.com

0
3/2/2004 11:57:16 AM
On Tue, 2 Mar 2004 05:41:48 -0600, loscherland
<<loscherland.12ho7m@excelforum-nospam.com>> wrote:

>Is there any way I can extract a date from a cell containing a line of
>text???
>
>Eg:
>A1: "16-02-04-Received & Completed"
>
>How can I extract the date "16-02-04" & display it in Cell B1???
>
>
>---
>Message posted from http://www.ExcelForum.com/

If it's always in that format, then:

 =DATE(MID(A1,7,2)+1900+(--MID(A1,7,2)<30)*100,MID(A1,4,2),LEFT(A1,2))


--ron
0
ronrosenfeld (3122)
3/2/2004 12:19:02 PM
Reply:

Similar Artilces:

i cant see html on my incoming message (i see only plain text)
when i receive email all i see are the hyperlinks and plain text how do i change this so i can read the entire email and see photos and flash. ...

Remove GUID From Email Subject Line
I have version 1.2 installed and have complaints that our emails are being bounced because of the GUID on the subject line is being interpited as spam. I have heard that there is an option to not have it included on emails but I can not find the code or instructions to make it happen. From what I have read it seems simple and you would think in 1.2 it would be a config option. I have searched the newsgroups and online but the only references I can find require a login which I do not have. We are a Microsoft Certified Partner and a CTEC but not an MBS Partner which seems different since none ...

Reading Pane Text Size
Okay, if you click on the grey area surrounding the reading pane in Outlook 2003, you can adjust the text size to largest. Is there a way to make this permanent? Thanks! Kevin Not that I know of, but if you click in the message body, then hold down the control button and scroll, you can adjust the reading font to your preference. This must be done on a per message basis, however. --� Milly Staples [MVP - Outlook] Post all replies to the group to keep the discussion intact. All unsolicited mail sent to my personal account will be deleted without reading. After furious head scratching...

Getting text to read bottom to top
Good afternoon. I have text in a text box that I'd like to read from bottom-to-top rather than left-to-right. I can rotate the text box, but the text itself isn't rotating. Click the Text Direction button (in Word 2003 or earlier it's on the Text Box toolbar; in Word 2007/2010 it's on the Text Box Tools ribbon). The first click changes the direction to top-to-bottom and the second click changes it to bottom-to-top. A third click will return it to left-to-right. -- Regards, Jay Freedman Microsoft Word MVP FAQ: http://word.mvps.org Email cannot be ack...

Locking Text Boxes in a Form using VBA
I have designed a form with 26 textboxes. Each is name in the form of txtName, txtAmount and so forth. I would like to lock these boxes with a few lines of code without having to put a line in for each box. Maybe something in the order of For each TextBox in Worksheet .locked = true. but I haven't been able to figure out how to do it. I found a 'For Each' example in Help but it appears not to apply to text boxes. I know I can do it with individual lines for each box but had much rather use a looping method. Then, if I added some more text boxes I wouldn't ...

Find all text instances in a sheet and add one number from each row
I'm trying to have a folmula on a sheet that will look back at another sheet and find all the instances of a text string and then add a numeric value on that row with all the numbers that contain the same text. For instance I have a summary sheet that lists test names and the total hours associated with that test. I want to look back at another sheet and find each instance that has the same test name and add the hours together. This is what I want my summary sheet to do: Summary Sheet ID Test Name Hours 1 Test1 5 2 Test2 4 Using this data: Data Sheet ID ...

how to change what text and feilds r sent as a "return Receipt"
Would like to change what is sent when I send a Return Receipt to someone. can't seem to find this anywhere i any documentation Splash wrote: > Would like to change what is sent when I send a Return Receipt to someone. > can't seem to find this anywhere i any documentation The sender of an e-mail with the headers requesting a "read/delivery receipt" cannot specify how that acknowledgement e-mail is composed. Ok, Where is this information pulled from? At two locations email is sent and recieved. when sent from M1 a reead receipt is requested. At M2 the m...

How to show save date
Need a function or macro to show the save date of the actual file in any cell. How to proceed. How could such a macro be started automatically when the file is opened. Thanks, Kurt Right click on the Excel Icon just left of the File menu. Select view code and the paste in the following workbook_open event: Private Sub Workbook_Open() Sheets(1).Range("A1").Value = _ 'Change as required Me.BuiltinDocumentProperties("Last Save Time").Value End Sub Hope this helps Regards Rowan Kurt Mayer wrote: > Need a function or macro to show the save date of t...

Positioning dates on chart
I am running an Excel 97 macro which updates charts. All the charts use the same long macro and change based on one data text input. Most of the charts have the dates along the bottom of the chart where I want them but a few have the dates either at the top of the chart or somewhere in between. Could someone tell me what the key settings are for having dates positioned along the bottom of the chart? Then I would put a new command at the end of the macro to ensure that these dates will be properly placed. Any suggestions would be greatfully appreciated. Thanks, Roger __________...

Sorting dates before 1900
I have set up a spreadsheet containing a column that lists nearly 50 dates prior to 1900 in format DD/MM/YYYY. I am trying the sheet o several columns into date order but it simply orders into DD/MM order can you help please? Ken Walte Attachment filename: ken tree.xls Download attachment: http://www.excelforum.com/attachment.php?postid=52227 -- Message posted from http://www.ExcelForum.com Ken This macro assumes your Dates are contigious in Column A (with no blank cells). Also assumes your Data is 1000 rows deep and from Column A to M wide. HTH Sub Sort_Pr...

Text in Report to be Justified
Hello I am using Access 2003. I have a field L1 with data type Memo. In the report the alignment option available is Left, Center or Right. How to have the text alignment in field L1 to be justified so that the text in my report are justified. Thanks in advance. Biju/Kuwait What type of control are you using on your report? I also have an option of "Distribute" which will do what you are asking. Are you using a text box? -- Hope that helps! RBear3 .. "Biju George" <bijuleena71@hotmail.com> wrote in message news:u4%23pnlyiHHA.4112@TK2MSFTNGP04.phx.gbl... ...

Pivot table > date filters. Any way to set to most recent days?
Hello, I have a pivot table that's based on data that has a column for dates. If I want to filter the dates, so that I get the most recent 14 days, I can set this using "between" but then I'd have to adjust that every day that I look at the data. Is there a way to set this dynamically so that it always shows the most recent 14 days? Furthermore, I'd like to create a pivot table field (or something that will do this automatically) that calculates the average for a particular column over my date range (recent 14 days). Is this doable? ...

text to column code / invalid syntax
I was trying to use this code to automatically delimit cells upo opening the workbook but it is not working. Can someone tell me what i going wrong? Sub auto_open() Columns("A:A").Select Selection.TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _ TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=False, _ Semicolon:=False, Comma:=False, Space:=True, Other:=False, FieldInfo _ :=Array(Array(1, 1), Array(2, 1)), TrailingMinusNumbers:=True End Sub see example below ... Thanks, Vat -- Message posted from http://www.ExcelForum.com Hi what exactly ...

release date for GP 9.0, spanish version.
Someone knows what is the date to release GP 9.0, spanish version? Thanks. -- Evelin. Hi Evelin, can I contact you about the Spanish version. We are starting to do some research. Thanks "Ana Evelin Rosa" wrote: > Someone knows what is the date to release GP 9.0, spanish version? > > Thanks. > > -- > Evelin. Sure... this is my email address: anar@segacorp.com Bye. -- Evelin. "Zach Morgan" wrote: > Hi Evelin, can I contact you about the Spanish version. We are starting to > do some research. > > Thanks > > "Ana Ev...

comparing a date with "today" in ACCESS Query
My table has a DOB date. I wish to compare it with today, and count those rows where DOB is under 16 years, and also count those greater than 16 years. I thought this would work : datediff("yyyy", CurrentDependents.DOB, Date()) < 17 and datediff("yyyy", CurrentDependents.DOB, Date()) > 16. Alas not. If I set DOB to 01/01/1994, I get < 17 set to 1, and > 16 set to 0. If I set DOB to 31/12/1993, I get < 17 set to 0, and > 16 set to 1. So my syntax is only looking at the year element of the date, and not the whole date. Can anyone advise pleas...

Query on Blank Date
I would like to do a query on "Blank Date". For example, i have 10 fields for my user to fill up. The date shall be input only when his customer present the discount card. So end of year, he wish to know how many slots from users in the database unfilled. i tried the following [Information]![Date_1]>#1/1/2008# And [Information]![Date_2]=Null [Information]![Date_1]>#1/1/2008# And [Information]![Date_2]='' [Information]![Date_1]>#1/1/2008# And [Information]![Date_2]="" it doesnt work. Please help. Thanks. On Thu, 17 Jan 2008 07:16:47 GMT, "yonggi...

1st line needs to be just the name no # in beginning, how?
I just typed a list of names and other information from line 1 to line 30. I also have the heading on top of each column. That line also has a number 1 in the front, how can I start the 2nd line as number 1, which is actually the first person in the list, and then 1 to 30 down the list etc etc. I know it is probably very easy to do, but I have a block, a mental block right now. How do I change things, please have mercy and let me know.......Peter If you already have a blank column ready for the numbers then great, but if not then just select Col A and do Insert / Columns. Now in cell A2 p...

Coloring Alternate lines
I want to color columns A to E on every other line. I can't find an ISEVEN or ISINTEGER function, so how do I do That in VB? Thanks, Jim Berglund I'm getting a Next without For error in the following code, and can't figure out why... BTW, If I get this fixed, will this code work for coloring alternate lines? Jim Sub SortAndColor() Dim q, i As Long With ActiveSheet .Rows("1:1").Delete Shift:=xlUp q = .Range("A" & Rows.Count).End(xlUp).Row .Sort.SortFields.Clear .Sort.SortFields.A...

Outlook 2003
I'm trying to create a VBA script that will be executed via the command line (I'm integrating into another application, it's my only option) What I would like to do is create a VBA script that will have it's variables set via the command line, but cannot find the code. Is it possible? more or less something along the lines of : "C:\Program Files\Microsoft Office\Office\Outlook.exe" /c ipm.note /altvba otmfilename -v1=string or ?v1=string Is something like this possible by defining it in the subroutines? eg. Sub CreateOutlookTask(v1 as String) The mai...

scatter chart insists on plotting as a line chart
Excel 2003: had 2002 version. Data plotted correctly. Next time I opened file, data displayed as a series rather than a scatter chart, though the data range fields for x and y coordinates display the proper ranges. Tried rearranging my data into adjacent columns, creating new charts and ranges, upgrading to 2003 Excel, changing chart into other types and back again...am I missing a primary concept? ...

Problem with date formatting
I have created a vbsript that runs from a batch file when users logon to our domain. In the script the date & time of a file on our server is checked & added to a mysql database along with other info. We are in the UK & have our date set to UK format, so in order to get the date into mysql I have to transpose it into yyyy-mm-dd; this works fine for all but 4 of our PCs. Upon closer inspection it seems these PCs were set to 'English (United States)' in 'Regional & language options' in control panel. I have therefore changed these to 'English (...

Concatenating date field into text ands using in a formula
Hi all, Hope you can help. I have a date field (B1) and am trying to concatenate this into a directory path as part of a formula to pull data from another worksheet. IE My worksheets are named open"date of report".xls B1 contains "date of report" I have tried concatenating the worksheet name in a field and then using that field. I have tried cocatenating the directory path in a field and using that field. My last attempt at a formula is =SUM(IF('=concatenate("D:\CSA\Reporting\Open Jobs\[open"&TEXT(B1,"ddmmyy")&".xls]Customer")...

Get the date for the latest record ?
I have a Table called TReport which have a field called daReportDate, that holds the date where the record was created. I also have a field, id, that is a autonumber field. I now want to get the date for the latest post. I have done it this way - SELECT DateValue(daReportDate) FROM TReport WHERE id = (select Max(id) from TReport) - it seems to work, but is it the right way to do it ? -- My Photo Gallery www.anothermasterpiece.com SELECT Max(daReportDate) FROM TReport "SpookiePower" <boxjunk2600@gmail.com> wrote in message news:%23gVa$feTIHA.4656@TK2MSFTNGP03.phx.gbl... ...

Can I program Excel cells to change colour at a set date?
I want certain cells to change from yellow to green when a date contained within those cells is reached. Excel version 2003 Use a conditional format. For instane you might format as yellow, then set a conditional format based on the formula =TODAY()>=datecell to apply the green color. Jerry sunflowermurray wrote: > I want certain cells to change from yellow to green when a date contained > within those cells is reached. Excel version 2003 ...

Remove Group Box border lines (without using macro)?
Is there a non-macro solution to removing the Group Box border lines? If not, a macro solution would also be appreciated, but as it is going into a multi-user form, I don't want to scare the users with macro warnings. ;) Thank you! -Meghan I don't think you can do this except via a macro. But you could run the macro to hide those borders and release the workbook with no macros. In fact, you don't even need a macro--just just need the code. Hit alt-f11 to get to the VBE hit ctrl-G to see the immediate window type this in and hit enter: activesheet.groupboxes.visible = false ...