turn number into text

[ammartino44]

Hello. Is there a formula I can use that will look at the following in an excel cell Daily Numbers Report - Summary_2014-01-26.xls
and then convert that into the date value 41,300. And the same thing for all other cells with the same format...so if was Daily Numbers Report - Summary_2014-01-23.xls the formula would convert it to 41,297. Thanks

[Olly]

I think you want this:

=DATEVALUE(MID(A1,FIND("Summary_",A1)+8,10))

But the serial date of 26-01-2014 is 41,665, not 41,300... 41,300 is 26-01-2013

[Pete_UK]

If your text is in A1, you can use this formula:

=DATE(MID(A1,FIND("_",A1)+1,4),MID(A1,FIND("_",A1)+6,2),MID(A1,FIND("_",A1)+9,2))

ensure that the cell with the formula in is formatted as a Number.

Hope this helps.

Pete

[AlKey]

Not sure if you want to return the entire string or just the number replacing date.

So if you need just the number:

=TEXT(--MID(A1,FIND("_",A1)+1,10),"#,000")

If you need the string with the number:

=LEFT(A1,LEN(A1)-14)&TEXT(--MID(A1,FIND("_",A1)+1,10),"#,000")

	A	B
1	Daily Numbers Report - Summary_2014-01-26.xls	Daily Numbers Report - Summary_41,665