• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 167
  • Last Modified:

Excel extra line

I need major help.... I have an huge excel file... many cells have multiple lines (2 lines). The 1st line has text and the 2nd line has one empty spot. I need to remove that blank spot (erase the second line). Instead of going into each cell and deleting it one by one (which will take me forever... there is about 1020 rows x 5 columns) is there a way to delete it all (get rid of that second line)....

see attached.

I'm using excel 2007
excel.JPG
0
MrMay
Asked:
MrMay
1 Solution
 
Patrick MatthewsCommented:
In another column, use a formula like this:

=TRIM(CLEAN(B1))

Copy that down as needed.

Then, copy the formula results, and use Paste Special / Values to copy over the original text.  Then delete the formulas.
0
 
Saqib Husain, SyedEngineerCommented:
You can also try this macro

Sub deltrailinglf()
Dim cel As Range
For Each cel In ActiveSheet.UsedRange.Cells
    Do While Right(cel, 1) = vbCr Or Right(cel, 1) = vbLf
        cel = Left(cel, Len(cel) - 1)
    Loop
Next cel
End Sub
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

Tackle projects and never again get stuck behind a technical roadblock.
Join Now