I need to extract dates from a column that contains other information. Format varies.

[LewisSR]

I have attached an image. As you can see, I am calling for the dates from the original data on the far right. Most of them are in the format DD-M/MM-YY, but some are D-M/MM-YY. When they are D-M-YY, they print as "- D-M-YY" as I am calling a fixed value (=RIGHT(CELL,8)) and when the date is D-MM-YY, they print as " D-MM-YY".

Ideally I would like to be able to call just the date, if possible have the data called formated so that it is consistent. So I need a formula that is a bit more flexible and can call from the cell containing "LOCATION - DATE" and print it in column B as "DD-MM-YY". I'd use the if isnumber search formula, but seeing as there are four variations I can't (DD-MM-YY, D-MM-YY, D-M-YY, DD-M-YY)

I need the dates consistent as, once complete I want to dock the headers and sort the rows by date.

Any constructive input would be appreciated. I've uploaded the document to the post too. Let me know if the image or attachment aren't showing, I am new here.

[mehmetcik]

Formula for B2 =TRIM(SUBSTITUTE(RIGHT(I2,LEN(I2)-FIND("-",I2)),"-","/"))*1

That turns the date into a number.

Format column B as you want it displayed.

[LewisSR]

Thank you for the quick reply! It worked for the first few, then started forgetting the days and only accurately converting month and year. Not sure why, the format doesn't differ at all from I6 to I7. I made the whole B column follow the same formula and noticed the same issue all the way down.

Again thanks for a quick response. Attachment 757447

[AliGW]

Attach the workbook, please, not a picture of it.

Administrative Note:

Members will tailor the solutions they offer to the version of Office (Excel) that you have. Please check that your forum profile is up-to-date in this respect. If you aren't sure, in Excel go to File | Account and report what it says below the MS logo at the top of that page. If your version is for Mac, please also state this. Thanks.

[samba_ravi]

try below formula
=LOOKUP(9^9,RIGHT(SUBSTITUTE(I2," ",""),{1,2,3,4,5,6,7,8,9,10})+0)

[LewisSR]

I have attached the file in the initial. I've updated my office version. I attached an image of the result of the formula suggested above.

[mehmetcik]

That does not work either because some of your entries do not have either of those characters.

So try

B2 =SUBSTITUTE(RIGHT(I2,LEN(I2)-MIN(IFERROR(FIND({0,1,2,3,4,5,6,7,8,9},I2),99))+1),"-","/")*1

[AliGW]

Try this instead:

=SUBSTITUTE(TRIM(SUBSTITUTE(RIGHT(I2,8),"–","")),"-","/")*1

[LewisSR]

Thank you Samba and Mehmetcik, both formulas worked for me. I'll do half and half . I've added reputation.

Have a good day.

[AliGW]

What about mine? It's a bit shorter.

[LewisSR]

Sorry, it hadn't refreshed when I marked as solved. Thank you also, third and third and third?

[mehmetcik]

Ali's formula returns errors in B55 to B74

So try

B2 =SUBSTITUTE(RIGHT(I2,LEN(I2)-MIN(IFERROR(FIND({0,1,2,3,4,5,6,7,8,9},I2),99))+1),"-","/")*1

[AliGW]

... third and third and third?

Don't understand - sorry.

I've added reputation.

That's kind - thank you.

[AliGW]

Ali's formula returns errors in B55 to B74

Damn!