Hidden Character

[rhmkrmi]

Hello,

I seem to have one hidden character at the front and another at the end of a text in a workbook.
I cannot find out what character it is and cannot remove if using CHAR(160) with CLEAN, TRIM, SUBSTITUTE.
I have only been able to find out that it is unicode 8237 at the beginning and unicode 8236 at the end.

Could you please help find a formula to remove these two hidden characters?
I have a VBA which is shown here but would like to use a formula.

Sub Kleanup()
Dim N1 As Long, N2 As Long
Dim Bad1 As String, Bad2 As String

N1 = 8237
Bad1 = ChrW(N1)
N2 = 8236
Bad2 = ChrW(N2)

Cells.Replace what:=Bad1, replacement:=" ", lookat:=xlPart
Cells.Replace what:=Bad2, replacement:=" ", lookat:=xlPart

End Sub

Thank you.

 

[KRice]

Since you are using Excel 365, you might want to investigate a LAMBDA function to interrogate your text string...such as this one:

ATOCHARS

ATOCHARS returns an array of individual characters from a one dimensional array of text strings with an option to remove non-printing characters. Horizontal ranges spill down rows and vertical ranges spill across columns. Thanks to RicoS and their TEXTTOARRAY function for the inspiration. Calls...

www.mrexcel.com

 

[Peter_SSs]

If it is at the beginning and end of all the values then would this do?

=MID(A1,2,LEN(A1)-2)

 

[Peter_SSs]

If it is at the beginning and end of all the values

If it is not as consistent as that then perhaps

=SUBSTITUTE(SUBSTITUTE(A1,UNICHAR(8237),""),UNICHAR(8236),"")

 

[rhmkrmi]

If it is at the beginning and end of all the values then would this do?

=MID(A1,2,LEN(A1)-2)

Fantastic, thank you so much.

 

[rhmkrmi]

If it is not as consistent as that then perhaps

=SUBSTITUTE(SUBSTITUTE(A1,UNICHAR(8237),""),UNICHAR(8236),"")

That's great, covers all basis. Thank you.

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.