Formula / Code to find date within strings

[ivhee00]

Hi Guys,

Can you give me formula or code to find the date within strings then replace it with blank. Below is the sample the 2nd column is the output data.

Original Data	New Data
ADMISSION DATE 01/01/2016 ER CASE	ADMISSION DATE ER CASE
ARRIVED/ 12/31/2015 ER CASE	ARRIVED/ ER CASE
ADMISSION DATE 01/01/2016	ADMISSION DATE
RELEASED DATE: 01/02/2015 ER CASE	RELEASED DATE: ER CASE
ADMISSION DATE: 01/20	ADMISSION DATE:
RELEASED: 01/20	RELEASED

[Tony Valko]

I didn't download your file.

This monster formula works on the posted sample data...

Data Range

	A	B	C
2	ADMISSION DATE 01/01/2016 ER CASE	------	ADMISSION DATE ER CASE
3	ARRIVED/ 12/31/2015 ER CASE	------	ARRIVED/ ER CASE
4	ADMISSION DATE 01/01/2016	------	ADMISSION DATE
5	RELEASED DATE: 01/02/2015 ER CASE	------	RELEASED DATE: ER CASE
6	ADMISSION DATE: 01/20	------	ADMISSION DATE:
7	RELEASED: 01/20	------	RELEASED:

This formula entered in C2 and copied down:

=TRIM(SUBSTITUTE(SUBSTITUTE(A2,TEXT(LOOKUP(1E100,--MID(A2,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A2&1234567890)),ROW(INDIRECT("1:"&LEN(A2))))),"mm/dd/yyyy"),""),TEXT(LOOKUP(1E100,--MID(A2,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A2&1234567890)),ROW(INDIRECT("1:"&LEN(A2))))),"mm/dd"),""))

[shukla.ankur281190]

May Be

=TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE( SUBSTITUTE( SUBSTITUTE( SUBSTITUTE( SUBSTITUTE( SUBSTITUTE( SUBSTITUTE( SUBSTITUTE(A2,1,""),2,""), 3,""),4,""),5,""), 6,""),7,""),8,""),9,""),0,""),"/",""))

[Tony Valko]

Tip...

You don't need to quote the numbers.

Also, you need to add a TRIM function to get rid of the double spaces in some of the cells.

[shukla.ankur281190]

Thanks for Tip Mr. Tony

[Tony Valko]

You're welcome!