left, mid?

[ammartino44]

Hello. What would be a good formula for turning this Daily Numbers Report - Summary_2014-01-26-06-02-17.xls into this Daily Numbers Report - Summary_2014-01-26.xls

I imagine it would be something with left, or mid, or something but not very good at those formulas. Thanks.

[FDibbins]

If that is in a cell - say A1, then maybe...
=LEFT(A1,SEARCH("_",A1,1)+10)&".xls"

[ammartino44]

Thanks. That works. Can you explain what the formula is doing? : )

[AlKey]

This one should work too

=SUBSTITUTE(A1,RIGHT(A1,13),"")&".xls"

[FDibbins]

Nice shortcut, Al

=LEFT(A1,SEARCH("_",A1,1)+10)&".xls"

Using your sample...Daily Numbers Report - Summary_2014-01-26-06-02-17.xls
1st thing, it searches for the position of the underscore in the string...
SEARCH("_",A1,1).....31
Then, we know that you have a date with "-" in it, that is 10 characters long, so...
SEARCH("_",A1,1)+10....41

This gives us the number of characters we need to use in teh LEFT() fucntion
=LEFT(A1,SEARCH("_",A1,1)+10).....Daily Numbers Report - Summary_2014-01-26

Finally, we add .xls to the end of that string...
=LEFT(A1,SEARCH("_",A1,1)+10)&".xls"

Hope that helps?