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

Deepk

Board Regular
Joined
Mar 21, 2018
Messages
105
Office Version
  1. 2016
Platform
  1. Windows
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.
 

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).
Select the cells you want to clean in the way you indicated and the try this macro out on them...
Code:
[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]
 
Upvote 0
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.
 
Upvote 0
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. :)
 
Upvote 0
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?
 
Upvote 0
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!:laugh:
 
Upvote 0
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. :)
 
Upvote 0
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.
 
Upvote 0
Select the cells you want to clean in the way you indicated and the try this macro out on them...
Code:
[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]
 
Upvote 0

Forum statistics

Threads
1,223,889
Messages
6,175,226
Members
452,620
Latest member
dsubash

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