Text or space issue

neasds

New Member
Joined
Nov 16, 2009
Messages
45
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
 

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).
Try using the function below by Rick Rothstein

VBA Code:
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
 
Upvote 0
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),"")

Cell Formulas
RangeFormula
B2:B51,J2:J51B2=LEN(A2)
C2:C51C2=TRIM(A2)
D2:D51D2=LEN(C21)
E2:H2,F3:H51E2=CODE(MID($C2,COLUMN(E2)-4,1))
I2:I51I2=TRIM(SUBSTITUTE(A2,CHAR(160),""))
E3:E51E3=CODE(MID(C3,COLUMN(E3)-4,1))
 
Last edited:
Upvote 0
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.
 
Upvote 0
Thanks for the clarification. Regardless, it is not removed by TRIM, is Char(160), and can be removed by Substitute.
 
Upvote 0

Forum statistics

Threads
1,223,099
Messages
6,170,107
Members
452,302
Latest member
TaMere

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top