Extract date from text

[silverxx12]

Hi

i wish to extract the date (dd/mm/yyyy format) from a text string.

However when i used the "isnumber" function it came up with false, i think that means the date is formatted in text ?

so i have a cell which says:

bought some on 01/01/2010 bananas

i just want the date in that format with the forward slashes

thanks in advance

 

[Peter_SSs]

Does this do what you want?

Excel Workbook
	A	B
1
2	bought some on 01/01/2010 bananas	1/01/2010
3
Extract Date

 

[silverxx12]

Hi

i tired that formula but have a #value error

it could be because the date is in text format ?


i think i figured it out there were to many characters so a letter was picked up hence the error

yes thats fine

thank you

 

[Peter_SSs]

Hi

i tired that formula but have a #value error

it could be because the date is in text format ?


i think i figured it out there were to many characters so a letter was picked up hence the error

Did you try the formula with the sample data you gave?

What was the actual cell value where the formula failed?

 

[Ingolf]

If date is always in this format mm/dd/yyyy or dd/mm/yyyy you can use this formula:

=DATEVALUE(MID(A1,FIND("/",A1)-2,10))

or change "," with ";"

 

[Sektor]

UDF cell format must be Date.

Function ExtractDate(rng As Range) As Date
    ' Tools -> Reference -> Microsoft VBScript Regular Expressions 5.5
    Dim re As New RegExp
    re.Pattern = "\d{2}/\d{2}/(\d{4}|\d{2})"
    ExtractDate = CDate(re.Execute(rng.Value)(0))
End Function

 

[silverxx12]

My one last problem is that the date which i now have in say cell A2 is lets say a number 44057 which when formatted as dd/mm/yyyy comes out to 01/01/2011.

i am trying to concatenate that cell and have issues now because excel thinks the cell is 44057 instead of 01/01/2011 - is there a way to convert it to text plz?

ty

 

[Sektor]

Function ExtractDate(rng As Range) As String
    ' Tools -> Reference -> Microsoft VBScript Regular Expressions 5.5
    Dim re As New RegExp
    re.Pattern = "\d{2}/\d{2}/(\d{4}|\d{2})"
    ExtractDate = re.Execute(rng.Value)(0)
End Function

 

[barry houdini]

If you just use the MID formula (without +0) form Peter's original suggestion, i.e.

=MID(A2,FIND("/",A2)-2,10)

then that will return a text value that you can concatenate.

If you want the result to be a true date then you can concatenate using TEXT function, e.g. with date in Z2

=TEXT(Z2,"dd-mmm-yy")&Z3

change the part in quotes to give the desired date format

 

[Peter_SSs]

My one last problem is that the date which i now have in say cell A2 is lets say a number 44057 which when formatted as dd/mm/yyyy comes out to 01/01/2011.

i am trying to concatenate that cell and have issues now because excel thinks the cell is 44057 instead of 01/01/2011 - is there a way to convert it to text plz?

ty

What did you use to extract the date successfully?

I would still like to see some more samples of what the original data looks like. Is that possible?