CLEAN() not dependable to remove non-printable characters?

[yky]

I copied this date "05/10/2020" from this thread. When trying to manipulate it, I found it behaving weirdly as its length is 11 and RIGHT(A1,4) returns "020" when it should return four characters. Furthermore, LEFT(A1,10), though looks on screen exactly like A1, is not the same as A1 as =LEFT(A1,10)=A1 returns FALSE. That leads me to suspect the string contains a non-printable character at the end. Yet, CLEAN() doesn't remove the non-printable character as LEN(CLEAN(TRIM(A1))) still returns 11. The function CLEAN() is supposed to remove non-printable characters and I have used it to do so. Now, it doesn't seem to be dependable. It seems from now on I'll need to double-check the result whenever I use CLEAN().

test1.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	05/10/2020	020	#VALUE!	"0"	"5"	"/"	"1"	"0"	"/"	"2"	"0"	"2"	"0"	""
2		2020	11
3		/2020	11
Sheet2

Cell Formulas
Range	Formula
B1	B1	=RIGHT(A1,4)
C1	C1	=RIGHT(A1)*1
D1:N1	D1	="""" & MID($A$1,COLUMN()-3,1) & """"
B2	B2	=RIGHT(A1,5)
C2	C2	=LEN(A1)
B3	B3	=RIGHT(A1,6)
C3	C3	=LEN(CLEAN(TRIM(A1)))

 

[yky]

Not sure about the workings in the background but it is quite common for a Replace to trigger Excel to do another assessment of what it believes is the best fit for the data (pretty similar to what Text to Columns does).


Because in the thread DanteAmor commented on you were using the VBA version of LEN, here you are using the formula version.
They behave differently (a bit like the VBA version of TRIM behaves differently to the formula version).

Thanks again for the great info..

 

[MARK858]

You're welcome

 

[amoverton2]

HI,

I think I'm in the right place with this post, if I need a new one please let me know... My problem is that I'm not as smart with Excel as most of you are.

Here's my problem, in the below, all of the dates have something before and after the date, if I manually go into the cell and delete what's before and after the cell, after hitting enter, it allows for new formatting of the date into what I want. I have a bunch of sheets with dates just like this and it would be painful to do it manually. I'm looking for a VBA code or formula that would clear out the hidden items within the cell that I don't know how to eliminate besides what I described above. Ideally, a code that runs after workbook activation that checks all the sheets (say for now 30 sheets with the A3:E11 cell range) removes the characters, and formats the cell to have a date formation of DD-MMM-YYYY. Clean and trim does not do the trick, and a couple of the VBA codes (CleanTrim, SmartTrim) I found while trying to figure this out hasn't worked, Please Help!

DEW_CTM.xlsb
	A	B	C	D	E
1	DATE1	DATE2	DATE3	DATE4	DATE5
2
3	06/01/2022	06/2025	08/08/2023	06/08/2025
4	02/01/2022	01/2024	07/27/2029	07/27/2029	04/29/2024
5	05/01/2020	11/2023	04/22/2025	04/22/2025	12/30/2023
6	02/01/2020	10/2023	08/09/2026	01/09/2027	01/22/2024
7	04/01/2022	08/2025	02/22/2025	02/22/2025
8	04/01/2022	03/2026	12/02/2024	12/02/2026
9	07/01/2023	12/2026	08/29/2026	08/29/2026
10			07/06/2026	07/06/2028	08/28/2023
11			02/15/2027	02/15/2027	11/12/2023
DEW_CTT

Thanks,
Adam

 

[Peter_SSs]

I think I'm in the right place with this post,

Duplicate to: What is in my cell????

In future, please do not post the same question multiple times. Per Forum Rules (#12), posts of a duplicate nature will be locked or deleted.

In relation to your question here, I have closed this older thread so please continue in your new thread.