String functions not affecting format

[toony]

Hi. If you can help me please. I have values column but it has simbles on each side of which I need to delete since I only need the numbers. Example: ~40464%
I have used the MID function and it does work. The problem is that the actual format is a date. I can't change the format because it has symbols on each side.
But when delete them with MID, it also cancels the format and doesn't let me to change it to date either. Tried to use the flashfill autofil but not successful.
=MID([@[Order Date]],2,5)

Is there a way to delete the 2 characters on the side of 40464 without deleting the existing date format?
I hope that the query is clear. If it is not apologies

 

[jtakw]

@Micron Yes, after seeing your picture in Post #7, I thought you were testing it in VBA, the "immediate" window is telling. So your solution works also, in VBA

@toony All 3 formulas should work, Post #2 and Post #4, the reason to add +0 ( or you can use *1 ) is to convert the result to numeric, if not, Excel sees the result as Text, that's the original reason why you can't change it to Time format. After +0, Excel sees it as Numeric, which represents the date, then you can format as you like.

EDIT: Doing Any math to a Text representation of a number converts it to a Real Number, the long way would have been to use VALUE function in front of the formula without +0, like:

=VALUE(REPLACE(REPLACE(A10,1,1,""),6,1,""))

 

[toony]

That's great. Thank you for explaining the +0 (or *1) so well. I completely get it now. Cheers

 

[jtakw]

That's great. Thank you for explaining the +0 (or *1) so well. I completely get it now. Cheers

You're welcome.