• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 162
  • 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

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

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