remove char(10)

G

Gaurav Karira

Board Regular
Joined
Jun 13, 2003
Messages
121
Hi VBA geeks,

I have file where in there are many cells with Alt + Enter in them ie char(10) now the number of such cells is so large that i cant manually remove them with " " ie a blank space.

Please VBA geeks come to my rescue.....can some advise some code which traces all such cells and then replaces char(10) with " "

Cheers
Gaurav
 
Sort by date Sort by votes
I got that but that isint doing me that help what i need.

reason being it trims all i mean i need to replace char(10) bu " " one space but it reoves char10 which makes my sentence wrong........pls help

cheers
gaurav
 
Upvote 0
Gaurav Karira said:
I got that but that isint doing me that help what i need.

reason being it trims all i mean i need to replace char(10) bu " " one space but it reoves char10 which makes my sentence wrong........pls help

cheers
gaurav
Click to expand...

If you want to, you can apply a formula:

=SUBSTITUTE(A1,CHAR(160)," ")
 
Upvote 0
Hi

Thx for the prompt response

Thats the whole problem I knew this formula but i cant apply this to 1560 cells i have so many char10 in sheet .................

can u guide me some better solution

I wd go nuts doing this formula as all cells are in diffrent columns n rows not consistent as to that i can drag the formula.....please advise

look forward to ur reply

cheers
Gaurav
 
Upvote 0
Try using the VBA Replace method (amend columns/range to suit your purpose)

<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> replacer()
<SPAN style="color:#00007F">Dim</SPAN> MyChar
MyChar = Chr(10)
Worksheets("Sheet1").Columns("A:H").Replace _
    What:=MyChar, Replacement:=" ", _
    SearchOrder:=xlByColumns, MatchCase:=<SPAN style="color:#00007F">True</SPAN>
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

Hope this helps
 
Upvote 0
Could an alternative be, if you type =Chr(10) in a cell, copy that and then use Edit, Replace and Ctrl+ C in "Find what" and " " in "Replace with"...?
 
Upvote 0
You must log in or register to reply here.

Similar threads

E
  • Question Question
Removing Blank Cells
Replies
2
Views
363
Peter_SSs
Peter_SSs
R
  • Solved
How to remove trailing Chr(10) character from a string
Replies
2
Views
314
RunTime91
R
B
  • Question Question
Excel Table Lookup with OR Conditions and Multiple Columns
Replies
1
Views
498
BenW71
B
Z
  • Question Question
VBA Complex Organizational Loop
Replies
1
Views
137
HighAndWilder
H
J
  • Question Question
VBA Error on Logic to Remove Rows based on Blank Cells
Replies
4
Views
471
Alex Blakenburg
A

Share this page

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