• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

How to Remove line breaks within cell instantly.

R

rumshar

Member
Hi All,
This is not a question but a trick I learned recently.
I have to deal with such excel files which are converted from pdf or are downloaded from internet. Most of the times there will be line breaks within the cell(Alt + Enter or Chr(10)). When I was very new to excel,to clean my data, I would get into each cell and hit F2 and remove those 'unseen' characters manually. Later I got to know about function called 'Clean' which helped me a lot. But to use Clean function I had to use helper column(s). Then came a time when I learned simple VBA (for next loop and worksheetfucntion(clean). I thought this is the best approach to do this task. But I was wrong.
These days I do the same work with the help of find and replace.
This is how I do it.
1. Select the Range
2. Hit Ctrl + H to invoke Find & Replace Dialog Box.
3. Type Ctrl + J in Find what: Box
4. Type space (or leave blank depending on your requirement) in Replace with: box
5. Alt + A
You are done...
Purpose of this thread is to share my knowledge, please don't laugh at me if it was known to you already!!
With Regards
Rudra
 
Thanks Rumshar.. for sharing the tip.. :)

In Ms-Word.. you can find few more Special Characters to replace...
Like ^p for Paragraph Mark, ^# for Any Number.. (^ is Ctrl)

In Word > Go to Find & Replace.
Click More>> and check Special Button..

Special Find & Replace.PNG
 
Debraj,
In all my experience, I've never known about that button! I've always tried to scour the internet trying to find the special key combination to search for these. :rolleyes:
 
@Luke M thanks for liking it...
@Debraj I rarely use word so hadn't seen this button at all.Thanks for sharing this. Looking at these options of MS Word, excel looks a 'poor cousin' of Word to me.

Cheers
Rudra
 
rumshar said:
@Luke M thanks for liking it...
@Debraj I rarely use word so hadn't seen this button at all.Thanks for sharing this. Looking at these options of MS Word, excel looks a 'poor cousin' of Word to me.

Cheers
Rudra
Click to expand...
I think you wanted to say it the other way round ;).

If you want to check this then you will find that there are few word forums [mostly sub forum of Main Excel or Office forum] and the same applies to books as well.

Of course, I do not mean any offense for word. I still maintain my CV in word format.

In fact in Word VBA there's a gem called Task object which can loop through all running windows task. It is little difficult/tricky to harness windows task (if you don't know task object) as then you need to use API calls or scripting codes from WMI.

Here's one reference thread on Chandoo
http://forum.chandoo.org/threads/how-to-play-a-song-with-the-starting-of-excel-file-solved.7727/
[code needs cleanup as it is from old forums]

If you want some similar API then here's one I had tried sometime back:
http://www.vbaexpress.com/forum/showthread.php?t=43576
 
You must log in or register to reply here.
Back
Top