Data Manipulation in Excel or Access - Linefeed

I am trying to parse some data.  In Excel it wraps on lines, but there is not Line Feed character.  

Can anyone help me figure out how to get this into one line per record?

I can use Excel or Access.

Sample data attached.
Troy OverholtAsked:
Who is Participating?
Bill PrewConnect With a Mentor Commented:
Okay, we can do that with a small VBA routine.

Is this the only column in the data, or are there other columns?

Bill PrewCommented:
There is definitely a line feed character at the end of each "sentence" that is being placed on a new line.

Do you want to clean the data up before it gets into Excel, or just as it currently stands?

Where did the data come from, how did you get it in Excel?

Bill PrewCommented:
One way to see this in Excel, is to place the following formula in B1 to remove the line feeds.  Copy down as needed...


Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

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

Troy OverholtAuthor Commented:
I need to take each line and create an individual line for each, instead of having it all in one cell.  I need to import to another format, which requires each activity to be separate.  If I import or paste into an Access table it is all one long string.  I wanted to be able to programmatically  search for the line feed and create a separate record.
Troy OverholtAuthor Commented:
The data came to me in Excel and it is my only opportunity to have the data, so no other options.
Troy OverholtAuthor Commented:
There are others, but this is healthcare data, so I cannot share the identifiers.  I used your formula to put a VISUAL placeholder in there, and sure enough it worked, so I think I can use Access to look for that character ("|") and separate them that way.
Bill PrewCommented:
Okay, for what it's worth here is a small VBA procedure that breaks up the cells with multiple lines of text.  If you take the sample sheet you posted originally, add this to it and then run it you will get the idea.  But it ignores all columns by A, so after it's done the other columns wouldn't align with the new column A rows.  Not sure if it is useful but since I had it I figured I pass it along.

If the "|" approach can work that's cool too.

Sub DoSplit()
    Dim arrTemp As Variant
    Dim strTemp As String
    Dim rngData As Range
    Dim lngRow As Long
    Dim shtSheet As Worksheet
    ' Define the range of cells to break apart lines (all of column 1)
    Set rngData = Range(ActiveSheet.Cells(1, 1), ActiveSheet.Cells(ActiveSheet.Cells.Rows.Count, 1).End(xlUp))

    ' Copy cell values into array
    arrTemp = rngData.Value
    ' "Flatten" array in to text string, placing <LF> between each cell's text
    strTemp = ""
    For lngRow = 1 To UBound(arrTemp, 1)
        If strTemp = "" Then
            strTemp = arrTemp(lngRow, 1)
            strTemp = strTemp & arrTemp(lngRow, 1) & Chr(10)
        End If
    Next lngRow

    ' Split temp string of all data at each <LF>
    ' This creates a new array with each line of text in a different element
    arrTemp = Split(strTemp, Chr(10))
    ' Copy the new array of lines back to the sheet, overwriting old data
    Set rngData = Range("A1")
    Set rngData = rngData.Resize(UBound(arrTemp), 1)
    rngData.Value = Application.Transpose(arrTemp)
End Sub

Open in new window

Troy OverholtAuthor Commented:
Thank you so much!
Bill PrewCommented:

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.

All Courses

From novice to tech pro — start learning today.