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]

Hi,

Try:

=MID([@[Order Date]],2,5)+0

Then format result cell as Date of your preference.

 

[Micron]

Does every cell contain one or both of those characters but no other unwanted characters? If so you could try the Replace function. You can also nest them into one line.
Replace(Replace([Order Date],"~",""),"%","")

 

[jtakw]

Does every cell contain one or both of those characters but no other unwanted characters? If so you could try the Replace function. You can also nest them into one line.
Replace(Replace([Order Date],"~",""),"%","")

@Micron , the proper syntax for REPLACE is in B10, I think you were thinking of SUBSTITUTE:

EDIT: Either way, +0 or something else (e.g. *1) should be added to convert the result to numeric, so it can be formatted as Date, like I shown OP in my post #2

Book3.xlsx
	A	B	C
10	~40464%	40464	40464
Sheet958

Cell Formulas
Range	Formula
B10	B10	=REPLACE(REPLACE(A10,1,1,""),6,1,"")+0
C10	C10	=SUBSTITUTE(SUBSTITUTE(A10,"~",""),"%","")+0

 

[Micron]

Never heard of SUBSTITUTE, so no. What I wrote works for me using the data provided:
?Replace(Replace("~40464%","~",""),"%","")
40464

 

[jtakw]

Never heard of SUBSTITUTE, so no. What I wrote works for me using the data provided:
?Replace(Replace("~40464%","~",""),"%","")
40464

Well, now you know about SUBSTITUTE.

I doubt REPLACE works the way you suggested, did you test it? - "Too few arguments" - error, see link:

REPLACE, REPLACEB functions - Microsoft Support

Syntax: REPLACE(old_text, start_num, num_chars, new_text) REPLACEB(old_text, start_num, num_bytes, new_text)

support.microsoft.com

 

[Micron]

What I wrote works for me using the data provided:

That means I tested it? However, not in a sheet, but perhaps I missed that that was a requirement. Maybe a picture would help.

 

[jtakw]

We are talking about a formula, correct? Try testing in a sheet please. If that works in an Excel sheet as a formula, you have a version of Excel that I (and probably MS) don't know about.

 

[Micron]

No, we're not. I'm using a vba expression. As I said, I never saw that it was a requirement to be a formula, which is why your solution is the right one.

 

[toony]

Hi all. Many thanks for your help. I tried =REPLACE(REPLACE(C2,1,1,""),6,1,"")+0 and it actually works with the correct date format .
Also tried =MID([@[Order Date]],2,5)+0 which works perfecto too. I am using MID function since it's shorter. However, if you can explain to me why +0 please just to understand the logic behind it I would appreciate it. Cheers