Text or space issue

[neasds]

When I try to run my pivot table the column with Amounts has either leading or trailing spaces and when I try to use TRIM it doesn't correct the problem. If I type in the numeric
it works. I've also tried clean but it doesn't work either. I have lots of rows of amounts and it will take forever to type in the numeric s. Any suggestions

 

[MARK858]

Try using the function below by Rick Rothstein

Function CleanTrim(ByVal S As String, Optional ConvertNonBreakingSpace As Boolean = True) As String
  Dim X As Long, CodesToClean As Variant
  CodesToClean = Array(0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, _
                       21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 127, 129, 141, 143, 144, 157)
  If ConvertNonBreakingSpace Then S = Replace(S, Chr(160), " ")
  For X = LBound(CodesToClean) To UBound(CodesToClean)
    If InStr(S, Chr(CodesToClean(X))) Then S = Replace(S, Chr(CodesToClean(X)), "")
  Next
  CleanTrim = WorksheetFunction.Trim(S)
End Function

 

[awoohaw]

If your data was copied from an internet page or some other source it may be using a zero length character that TRIM does not remove.
If you have 365 or 2021, and your text is in column A, do this on some of the records:

To remove Char(160) you need to use the substitue function
=Substitute(A2,char(160),"")

Mr Excel Questions 72.xlsm
	A	B	C	D	E	F	G	H	I	J
1	Raw Text	Raw Text Length	Trim Text	Trim Text Length	Code 1st Char	Code 2nd Char	Code 3rd Char	Code 4th Char	TRIM/ Substitute	Len TRIM/ SUBSTITUTE
2	EBX	5	EBX	4	69	66	88	160	EBX	3
3	CPQ	5	CPQ	4	67	80	81	160	CPQ	3
4	FMP	5	FMP	4	70	77	80	160	FMP	3
5	JEE	5	JEE	4	74	69	69	160	JEE	3
6	HBB	5	HBB	4	72	66	66	160	HBB	3
7	PJC	5	PJC	4	80	74	67	160	PJC	3
8	NPN	5	NPN	4	78	80	78	160	NPN	3
9	LIW	5	LIW	4	76	73	87	160	LIW	3
10	VKR	5	VKR	4	86	75	82	160	VKR	3
11	BBJ	5	BBJ	4	66	66	74	160	BBJ	3
12	MFT	5	MFT	4	77	70	84	160	MFT	3
13	VGS	5	VGS	4	86	71	83	160	VGS	3
14	BST	5	BST	4	66	83	84	160	BST	3
15	YOS	5	YOS	4	89	79	83	160	YOS	3
16	YAN	5	YAN	4	89	65	78	160	YAN	3
17	TIE	5	TIE	4	84	73	69	160	TIE	3
18	WJT	5	WJT	4	87	74	84	160	WJT	3
19	JLC	5	JLC	4	74	76	67	160	JLC	3
20	INI	5	INI	4	73	78	73	160	INI	3
21	KSK	5	KSK	4	75	83	75	160	KSK	3
22	TGT	5	TGT	4	84	71	84	160	TGT	3
23	ETB	5	ETB	4	69	84	66	160	ETB	3
24	IRX	5	IRX	4	73	82	88	160	IRX	3
25	NGL	5	NGL	4	78	71	76	160	NGL	3
26	RNP	5	RNP	4	82	78	80	160	RNP	3
27	ISN	5	ISN	4	73	83	78	160	ISN	3
28	UJO	5	UJO	4	85	74	79	160	UJO	3
29	APL	5	APL	4	65	80	76	160	APL	3
30	GCU	5	GCU	4	71	67	85	160	GCU	3
31	CVX	5	CVX	4	67	86	88	160	CVX	3
32	LNX	5	LNX	4	76	78	88	160	LNX	3
33	KFT	5	KFT	0	75	70	84	160	KFT	3
34	ESE	5	ESE	0	69	83	69	160	ESE	3
35	GRQ	5	GRQ	0	71	82	81	160	GRQ	3
36	UZB	5	UZB	0	85	90	66	160	UZB	3
37	OYG	5	OYG	0	79	89	71	160	OYG	3
38	LTG	5	LTG	0	76	84	71	160	LTG	3
39	OWU	5	OWU	0	79	87	85	160	OWU	3
40	EQG	5	EQG	0	69	81	71	160	EQG	3
41	OEB	5	OEB	0	79	69	66	160	OEB	3
42	FKQ	5	FKQ	0	70	75	81	160	FKQ	3
43	ULD	5	ULD	0	85	76	68	160	ULD	3
44	LTP	5	LTP	0	76	84	80	160	LTP	3
45	YZV	5	YZV	0	89	90	86	160	YZV	3
46	LEN	5	LEN	0	76	69	78	160	LEN	3
47	ZZG	5	ZZG	0	90	90	71	160	ZZG	3
48	QWH	5	QWH	0	81	87	72	160	QWH	3
49	KKZ	5	KKZ	0	75	75	90	160	KKZ	3
50	KQH	5	KQH	0	75	81	72	160	KQH	3
51	WCH	5	WCH	0	87	67	72	160	WCH	3
neasds

Cell Formulas
Range	Formula
B2:B51,J2:J51	B2	=LEN(A2)
C2:C51	C2	=TRIM(A2)
D2:D51	D2	=LEN(C21)
E2:H2,F3:H51	E2	=CODE(MID($C2,COLUMN(E2)-4,1))
I2:I51	I2	=TRIM(SUBSTITUTE(A2,CHAR(160),""))
E3:E51	E3	=CODE(MID(C3,COLUMN(E3)-4,1))

 

[Scott Huish]

If your data was copied from an internet page or some other source it may be using a zero length character that TRIM does not remove.
If you have 365 or 2021, and your text is in column A, do this on some of the records:

To remove Char(160) you need to use the substitue function
=Substitute(A2,char(160),"")

CHAR(160) is not a zero-length character. It is a non-breaking space, which means it's a space but words will not break lines with it like a normal space would.

 

[awoohaw]

Thanks for the clarification. Regardless, it is not removed by TRIM, is Char(160), and can be removed by Substitute.