Excel Trim Not Working 2

[PWD]

Good afternoon, I'm having trouble with data I have been sent as I need to find an exact match in Column A but am being defeated by what appear to be leading and trailing spaces. So I started off with:

=TRIM(A1)

I did some research and then tried:

=TRIM(SUBSTITUTE(A1,(CHAR(160)),""))

If I try to manually delete these "spaces" directly, in the formula bar, they still appear to be present but the final letters in the cell are removed instead. It's really weird because just trying to highlight these "spaces" appears to encroach on the next adjacent letter.

If I change the cell format Text direction from "Context" to "Left-to-Right" it appears to lose the trailing spaces. And the leading spaces if I choose "Right-to-Left".

If I search on Column A by just copying the cell and not by copying the contents of that cell, the Find box displays:

" Prompted awareness"

Copying the contents of the cell gives:

Prompted awareness

I have managed to replace " Prompted awareness" with "Prompted awareness" but I'd really like to find a fix for this in case it occurs with other entries. Has anyone seen this before?

Many thanks,
D€$

 

[MakeItSo]

Try copying the cell - not the content, but the cell - into Notepad++/PSPad/some editor capable of displaying control characters or hex codes.
See if you can determine the exact nature of that character. Its "character" so to speak.
Is it a space (dec 32), a non-breaking space (dec 160) or maybe a "thin space" (dec 8201) or a zero-width space(dec 8203) or ...?

Cheers,
MakeItSo

“Knowledge is power. Information is liberating. Education is the premise of progress, in every society, in every family.” (Kofi Annan)
Oppose SOPA, PIPA, ACTA; measures to curb freedom of information under whatever name whatsoever.

 

[PWD]

Hmmm... that was a thought, "How to identify these characters?" Unfortunately Notepad++ just displays:

" Prompted awareness"

but there appears to be an orange right-facing arrow prior to the text when I choose View; Show Symbol; Show All Characters.

Many thanks,
D€$

 

[MakeItSo]

That would be a tabulator then.
Spaces appear as orange/yellow dots, control characters as white-on-black/inverted character combination such as CRLF or US.
Try

=TRIM(SUBSTITUTE(A1,(CHAR(9)),""))

The you'll know if its a plain old tab.

“Knowledge is power. Information is liberating. Education is the premise of progress, in every society, in every family.” (Kofi Annan)
Oppose SOPA, PIPA, ACTA; measures to curb freedom of information under whatever name whatsoever.

 

[xlhelp]

I would try =Trim(Clean(A1))


Avoid Hangovers. Stay drunk.

 

[MakeItSo]

Why of course, xlhelp.
Clean should take care of all those nasties.


“Knowledge is power. Information is liberating. Education is the premise of progress, in every society, in every family.” (Kofi Annan)
Oppose SOPA, PIPA, ACTA; measures to curb freedom of information under whatever name whatsoever.

 

[PWD]

Wasn't sure exactly what clean might do - then it went out of my head. Interestingly clean didn't do the trick, but when I trimmed the cleaned cell it's all hunky dory - meaning that =Trim(Clean(A1)) is just what I needed!!


Many thanks,
D€$

 

[Deniall]

You can always extract the numerical code for the n th character in the text string in cell X99 with the formula
=CODE(MID(X99,n,1))
Then hopefully you'll have a better idea of what it is that is jamming your logs.