unable to remove paragraph-special characters from string values

RAYLWARD102

Well-known Member
Joined
May 27, 2010
Messages
524
I've loaded a data set from our accounting database, into an array. The accounting database users appear to be accidentally pressing the enter key in the description field, saving the data afterwards. When I dump my array containing all of this information, into an excel sheet, the description's (only some rows, 1 in every 400 records) expands the row height to double it's size, due to the invisible paragraph mark I presume. In excel, there is no way to see these characters, but I did find a handy dandy example for detecting if an unwanted character existed in the value: =IF(CLEAN(A1)=A1,"NA","Needs Cleaning")
Great, so I can identify that a cell has some invisible character resulting in the row height being doubled, but have no way, via VBA to remove such a character.
I've tried everything possible, and these marks wont go away; any idea's?

My trying to remove unwanted characters in a loop across fields containing unwanted characters.
Code:
  Eliminate = Chr(13) & Chr(10)
            For y = 2 To 6 'fields 2-6
                v(F(y), x) = Application.WorksheetFunction.Clean(CStr(v(F(y), x)))
                'v(F(y), x) = Replace(v(F(y), x), Chr(182), "")
                'v(F(y), x) = Replace(v(F(y), x), Chr(13), "")
                'v(F(y), x) = Replace(v(F(y), x), vbCrLf, "")
                'v(F(y), x) = Replace(v(F(y), x), ChrW$(244), "")
                'v(F(y), x) = Replace(v(F(y), x), Eliminate, "")
            Next y

as you can see, the application.worksheetfunction.clean can identify the bad character in a cell formula (see above formula example), but cannot be used to produce a clean value (clean by removing paragraph mark)
Pulling my hair out shortly....
 
Last edited:

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"

JoeMo

MrExcel MVP
Joined
May 26, 2009
Messages
17,461
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
Not certain this will work, but you can try it on a copy of your data. Select the data cells first, then run the macro.
Code:
Sub TrimALLMcRitchie()
   'David McRitchie 2000-07-03 mod 2002-08-16 2005-09-29 join.htm
   '-- http://www.mvps.org/dmcritchie/excel/join.htm#trimall
   ' - Optionally reenable improperly terminated Change Event macros
      Application.DisplayAlerts = True
      Application.EnableEvents = True   'should be part of Change Event macro
   If Application.Calculation = xlCalculationManual Then
      MsgBox "Calculation was OFF will be turned ON upon completion"
   End If
   
   Application.ScreenUpdating = False
   Application.Calculation = xlCalculationManual
   Dim cell As Range
   'Also Treat CHR 0160, as a space (CHR 032)
   Selection.Replace What:=Chr(160), replacement:=Chr(32), _
     lookat:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
   Selection.Replace What:=Chr(13) & Chr(10), replacement:=Chr(32), _
        lookat:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
   Selection.Replace What:=Chr(13), replacement:=Chr(32), _
        lookat:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
   Selection.Replace What:=Chr(21), replacement:=Chr(32), _
        lookat:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
   '---------------------------
   Selection.Replace What:=Chr(8), replacement:=Chr(32), _
      lookat:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
   Selection.Replace What:=Chr(9), replacement:=Chr(32), _
      lookat:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
   'Trim in Excel removes extra internal spaces, VBA does not
   On Error Resume Next
   For Each cell In Intersect(Selection, _
      Selection.SpecialCells(xlConstants, xlTextValues))
     cell.Value = Application.Trim(cell.Value)
   Next cell
   On Error GoTo 0
   Application.Calculation = xlCalculationAutomatic
   Application.ScreenUpdating = True
End Sub
 

RAYLWARD102

Well-known Member
Joined
May 27, 2010
Messages
524
problem with that approach is I don't plan on dumping to excel sheet; data will be accessed via user form, for printing labels (send it to external application). I only dumped to spreadsheet, to see if I was able to remove invisible characters (for testing).
Where I run into problems is when I send a field containing an unwanted paragraph mark to a label field; screws everything up.
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
49,456
Office Version
  1. 365
Platform
  1. Windows
Largely untested as I don't have any sample data or your entire code, but see if you can incorporate something like this into you code to see if it does the job.
Code:
Dim RX As Object

Set RX = CreateObject("VBScript.RegExp")
RX.Global = True
RX.Pattern = "(" & Chr(13) & Chr(10) & "?)|" & Chr(182) & "|" & vbCrLf & "|" & ChrW$(244)

For y = 2 To 6
  v(F(y), x) = RX.Replace(v(F(y), x), "")
Next y
 

Forum statistics

Threads
1,136,868
Messages
5,678,226
Members
419,754
Latest member
Sallylwy

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
Top