Double-click in each cell A1 within multiple rows simultaneously

[mmcclure]

I'm sure there is a way to do this but I am not able to figure it out. I have a spreadsheet with over 44,000 rows. Cell A1 in each row contains name and address (which I need in multiple columns and know how to achieve with text to columns). However, the way it is formatted from the source, they used a new line in the report (merging data from multiple fields in SQL). I found that if I double click in cell A1, it properly formats the name on the first line, address on the next two or three lines depending on the address while keeping everything in cell A1. I can then use text to columns to separate it out with Custom as the delimeter and CTRL + J to select the new line.

I just need a way to double click cells A1 through A44,000 simultaneously.

Thank you for any info you can give me that points me in the right direction.

 

[Dermot]

There's no need for doubleclicking, a little code will do it, and very fast indeed
It assumes your data is in column A

Sub SplitText()
  Dim i, D
  D = Cells(1, 1).CurrentRegion.Columns(1) 'get filled cells in column A
  For i = LBound(D) To UBound(D) 'loop through them
    D(i, 1) = Replace(D(i, 1), Chr$(13), Chr$(10)) 'replace linefeed char
  Next i
  Cells(1, 1).Resize(UBound(D) - LBound(D) + 1, 1) = D 'put the results back
End Sub

 

[Fluff]

Maybe

With Range("A1", Range("A" & Rows.Count).End(xlUp))
   .Value = .Value
End With

however if you need to replace a carriage return with a line feed

Range("A:A").Replace Chr(13), Chr(10), xlPart, , , , False, False

 

[mmcclure]

There's no need for doubleclicking, a little code will do it, and very fast indeed
It assumes your data is in column A

Sub SplitText()
  Dim i, D
  D = Cells(1, 1).CurrentRegion.Columns(1) 'get filled cells in column A
  For i = LBound(D) To UBound(D) 'loop through them
    D(i, 1) = Replace(D(i, 1), Chr$(13), Chr$(10)) 'replace linefeed char
  Next i
  Cells(1, 1).Resize(UBound(D) - LBound(D) + 1, 1) = D 'put the results back
End Sub

This took care of it and fast! Thank you so much!