0
Data  -----------------| ----- Formula ---|------------------- Date (Output) ------------------------------ 

(name - Aug 7, 2015) --------------------------------------------------------- 07/08/15


| -'}{[]//-;'; (lots of characters) (name - Aug 7, 2015) -------------------07/08/15

the length always varies to a great degree (name also changes - jan 27, 2015) - 27/01/15

Varied text (name - Aug 17, 2015) ----------------------------------------- 17/08/15

The issue with the data is that it uses several characters and varies constantly and that some of the dates are displayed one digit shorter, (e.g. (name - Aug 17, 2015) (name - Aug 7, 2015)) making it difficult to use =right and =mid as the position changes (stopping excel from reading as a date. I have included pictures below as I know my data isn't displayed in a clean way, apologies in advance.)

Here are some of the formulas that I have tried: 

 =DATEVALUE(MID(A2,FIND("-",A2)+1,LEN(A2)- FIND("-",A2)-1))
  • couldn't return the correct value as there were too many symbols.
=RIGHT(A2,14) , =SUBSTITUTE(RIGHT(C2,14),")","") , =datevalue
  • couldn't pick up dates with one digit on the day.

Data

with formulas

Brian Tompsett - 汤莱恩
  • 5,753
  • 72
  • 57
  • 129
G198
  • 43
  • 6

1 Answers1

1

It is a bit longer version of your first formula but it works with examples you provided:

=DATEVALUE(MID(A1,FIND("@",SUBSTITUTE(A1,"-","@",(LEN(A1)-LEN(SUBSTITUTE(A1,"-","")))/LEN("-")))+1,LEN(A1)- FIND("@",SUBSTITUTE(A1,"-","@",(LEN(A1)-LEN(SUBSTITUTE(A1,"-","")))/LEN("-")))-1))

It removes all "-" but the last one and does the same as you did with the rest of text.

zipa
  • 27,316
  • 6
  • 40
  • 58