Clean and Trim Data from a column without removing alt-enter.

[Deepk]

Hey,

As my last post regarding is not addressed I am posting this again.

I want to clean (removing non-printable character) and trim (removing extra spaces at the beginning and end) my data in selection within a column. As the clean function also remove alt-enter, I want alt-enter to be removed from the start and end of the data but not from the middle of the data (string).

Please help me with this. Thank you in advance.

 

[Rick Rothstein]

I realized that this code is leaving an extra space at the end of cleaned/trimmed value. Also, if the alt-enter is three or more at the beginning of the data, it is leaving one alt-enter at the beginning.

I cannot seem to duplicate what you have written above. Can you post a copy of a workbook that contains text which you don't think my code handles correctly to DropBox so that I can test my code directly on it?

 

[Deepk]

I cannot seem to duplicate what you have written above. Can you post a copy of a workbook that contains text which you don't think my code handles correctly to DropBox so that I can test my code directly on it?

Hi Rick,

Thank you for your swift response.

I can not share or upload anything from my workplace (policy under force). I am trying to simplify my data here.

Please enter the following data in a Cell:

Three alt-enters > "January" > four spaces > four alt-enters.

copy this Cell and paste in a range having more than one column and more than one row (4X4 matrix). Run the code. you will find one alt-enter at the beginning of each Cell and one space at the end.

I hope this is clear.

 

[Rick Rothstein]

Please enter the following data in a Cell:

Three alt-enters > "January" > four spaces > four alt-enters.

copy this Cell and paste in a range having more than one column and more than one row (4X4 matrix). Run the code. you will find one alt-enter at the beginning of each Cell and one space at the end.

I do not see the leading Alt+Enter (so until you can post a copy of a workbook where the text leaves the leading Alt+Enter, I cannot help you with that part of your problem) but I do see the trailing space. This fixes the trailing space issue...

[table="width: 500"]
[tr]
	[td]Sub CleanTrimKeepLineFeeds()
  Dim X As Long, S As String, CodesToClean As Variant, Cell As Range
  CodesToClean = Array(0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 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)
  For Each Cell In Selection
    S = Replace(Cell.Value, Chr(160), " ")
    For X = LBound(CodesToClean) To UBound(CodesToClean)
      If InStr(S, Chr(CodesToClean(X))) Then S = Replace(S, Chr(CodesToClean(X)), "")
    Next
    Cell.Value = Application.Trim(Replace(Replace(Replace(Replace(Application.Trim(Replace(Replace(WorksheetFunction.Trim(S), " ", Chr(175)), vbLf, " ")), " ", vbLf), Chr(175), " "), " " & vbLf, vbLf), vbLf & " ", vbLf))
  Next
End Sub[/td]
[/tr]
[/table]

 

[Deepk]

I do not see the leading Alt+Enter (so until you can post a copy of a workbook where the text leaves the leading Alt+Enter, I cannot help you with that part of your problem) but I do see the trailing space. This fixes the trailing space issue...

Rick,

yes, the trailing space gone.

I would like you to try once again. please run the codes with cells having following data

Two alt-enters > three spaces > one alt-enter > "January" > four spaces > four alt-enters

I am sure you will identify the issue I am facing. Sorry for the inconvenience.

 

[Rick Rothstein]

Rick,

yes, the trailing space gone.

I would like you to try once again. please run the codes with cells having following data

Two alt-enters > three spaces > one alt-enter > "January" > four spaces > four alt-enters

I am sure you will identify the issue I am facing. Sorry for the inconvenience.

I think this revision to my macro will now work correctly...

[table="width: 500"]
[tr]
	[td]Sub CleanTrimKeepLineFeeds()
  Dim X As Long, S As String, CodesToClean As Variant, Cell As Range
  CodesToClean = Array(0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 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)
  For Each Cell In Selection
    S = Replace(Cell.Value, Chr(160), " ")
    For X = LBound(CodesToClean) To UBound(CodesToClean)
      If InStr(S, Chr(CodesToClean(X))) Then S = Replace(S, Chr(CodesToClean(X)), "")
    Next
    Cell.Value = Replace(Replace(Application.Trim(Replace(Replace(Replace(Replace(Application.Trim(S), vbLf & " ", vbLf), " " & vbLf, vbLf), " ", Chr$(175)), vbLf, " ")), " ", vbLf), Chr$(175), " ")
  Next
End Sub[/td]
[/tr]
[/table]

 

[Deepk]

I think this revision to my macro will now work correctly...

[TABLE="width: 500"]
<tbody>[TR]
[TD]Sub CleanTrimKeepLineFeeds()
  Dim X As Long, S As String, CodesToClean As Variant, Cell As Range
  CodesToClean = Array(0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 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)
  For Each Cell In Selection
    S = Replace(Cell.Value, Chr(160), " ")
    For X = LBound(CodesToClean) To UBound(CodesToClean)
      If InStr(S, Chr(CodesToClean(X))) Then S = Replace(S, Chr(CodesToClean(X)), "")
    Next
    Cell.Value = Replace(Replace(Application.Trim(Replace(Replace(Replace(Replace(Application.Trim(S), vbLf & " ", vbLf), " " & vbLf, vbLf), " ", Chr$(175)), vbLf, " ")), " ", vbLf), Chr$(175), " ")
  Next
End Sub[/TD]
[/TR]
</tbody>[/TABLE]

Excellent! Thank you, and have a good day ahead.