Deleting second line in a cell

Hello

I have a dataset where the address and ZIP columns contain 2 lines of data, please see the attached file.

However, the first line of the address and zip is correct and the second line isn't

How can I keep only the first line of the cell and delete the second one? There are about 49K records in the final file


Many thanks

Hi,

Here is some VBA code that should get rid of the 2nd line.

Sub Delete2ndLine()

' Define Variables
Dim LastCol, LastRow, i, j, k As Long

' Get LastCol and LastRow with Data
LastCol = Cells(1, Columns.Count).End(xlToLeft).Column
LastRow = Cells(Rows.Count, 1).End(xlUp).Row

' Go through data and delete 2nd line, if there is one
For i = 1 To LastRow
   For j = 1 To LastCol
      k = InStr(Cells(i, j), Chr(10)) -1
      If (k > 0) Then Cells(i, j) = Left(Cells(i, j), k)
   Next j
Next i

' Give Completed Message
MsgBox "Complete."

End Sub

Hope that helps,

Dan

Enter this in C2 and fill down. When done, you can select column C, copy and paste values to get rid of the formulae then delete column B

Formula:

=LEFT(B2,FIND(CHAR(10),B2))

	A	B	C
1	address	zip
2	845 Sir Thomas Ct Ste 12 7 Carousel Cir	17109-4843 17033	17109-4843
3	33 N Duke St 130 E Woods Dr	17602-2842 17543-8651	17602-2842
4	3000 Two Logan Sq 417 Lindy Ln	19103-2799 19004-1334	19103-2799
5	1200 Wilson Dr	19380-4262 19382	19380-4262
6	124 N 4th St 219 Arch St	17801-2211 17801-2220	17801-2211