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]

Select the cells you want to clean in the way you indicated and the try this macro out on them...

[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(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]

Select the cells you want to clean in the way you indicated and the try this macro out on them...

Thank you Rick. You saved my tons of time.

 

[Peter_SSs]

As the clean function also remove alt-enter ..

If you have been trying a worksheet formula and if there is at most one Alt+Enter at the start/end (or at least you want to remove at most one) then you could try this worksheet formula.

=SUBSTITUTE(TRIM(CLEAN(LEFT(A1,1)&SUBSTITUTE(MID(A1,2,LEN(A1)-2),CHAR(10),"@@")&RIGHT(A1,1))),"@@",CHAR(10))

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

Please refer to #12 of the Forum Rules and points 6 & 7 of the Forum Use Guidelines.

 

[Deepk]

If you have been trying a worksheet formula and if there is at most one Alt+Enter at the start/end (or at least you want to remove at most one) then you could try this worksheet formula.

=SUBSTITUTE(TRIM(CLEAN(LEFT(A1,1)&SUBSTITUTE(MID(A1,2,LEN(A1)-2),CHAR(10),"@@")&RIGHT(A1,1))),"@@",CHAR(10))



Please refer to #12 of the Forum Rules and points 6 & 7 of the Forum Use Guidelines.

Dear Peter,

Thank you for your message and especially for the Forum Rules and Guidelines. I can assure you this will not be repeated again and and will stick to these guidelines. Thank you.

 

[Peter_SSs]

Dear Peter,

Thank you for your message and especially for the Forum Rules and Guidelines. I can assure you this will not be repeated again and and will stick to these guidelines. Thank you.

No problem, we understand that you are new here and may not have studied the rules much before now.


Was the formula any use to you, or were you looking for a macro solution anyway?

 

[Deepk]

No problem, we understand that you are new here and may not have studied the rules much before now.


Was the formula any use to you, or were you looking for a macro solution anyway?

Thank you. To be very honest, I have not used your formula as I was looking for macro solution and Rick as provided an excellent solution. See you guys in my next thread!

 

[Peter_SSs]

To be very honest, I have not used your formula as I was looking for macro solution ..

That's fine, but if you are looking for a particular method in future, it would be a good idea to state that in your opening post so helpers know whether to participate or which way to direct their efforts.

 

[Deepk]

That's fine, but if you are looking for a particular method in future, it would be a good idea to state that in your opening post so helpers know whether to participate or which way to direct their efforts.

Ohh, I missed to mention that. I will pay attention on this aspect during a post. Thank you for your guidance.

 

[Deepk]

Select the cells you want to clean in the way you indicated and the try this macro out on them...

[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(Replace(Replace(Application.Trim(Replace(Replace(WorksheetFunction.Trim(S), " ", Chr(175)), vbLf, " ")), " ", vbLf), Chr(175), " "), " " & vbLf, vbLf), vbLf & " ", vbLf)
  Next
End Sub[/TD]
[/TR]
</tbody>[/TABLE]

Hi Rick,

Hope you are doing good.

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.

Please update the code accordingly to rectify this. Find below my data to work on. I am selecting multiple consecutive column at once. Thank you.

[TABLE="class: grid, width: 192"]
<colgroup><col span="3"></colgroup><tbody>[TR]
[TD]January

[/TD]
[TD]January

[/TD]
[TD]January

[/TD]
[/TR]
[TR]
[TD] February

[/TD]
[TD] February

[/TD]
[TD] February

[/TD]
[/TR]
[TR]
[TD]


March

[/TD]
[TD]


March

[/TD]
[TD]


March

[/TD]
[/TR]
[TR]
[TD]

April

[/TD]
[TD]

April

[/TD]
[TD]

April

[/TD]
[/TR]
[TR]
[TD]May

[/TD]
[TD]May

[/TD]
[TD]May

[/TD]
[/TR]
[TR]
[TD] June

[/TD]
[TD] June

[/TD]
[TD] June

[/TD]
[/TR]
[TR]
[TD]


July

[/TD]
[TD]


July

[/TD]
[TD]


July

[/TD]
[/TR]
[TR]
[TD]

August

[/TD]
[TD]

August

[/TD]
[TD]

August

[/TD]
[/TR]
[TR]
[TD]September

[/TD]
[TD]September

[/TD]
[TD]September

[/TD]
[/TR]
[TR]
[TD] October

[/TD]
[TD] October

[/TD]
[TD] October

[/TD]
[/TR]
[TR]
[TD]


November

[/TD]
[TD]


November

[/TD]
[TD]


November

[/TD]
[/TR]
[TR]
[TD]

December

[/TD]
[TD]

December

[/TD]
[TD]

December

[/TD]
[/TR]
</tbody>[/TABLE]