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.
EEExample.xlsx
Troy OverholtAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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?


»bp
0
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...

=SUBSTITUTE(A1,CHAR(10),CHAR(32))


»bp
0
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.
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Troy OverholtAuthor Commented:
The data came to me in Excel and it is my only opportunity to have the data, so no other options.
0
Bill PrewCommented:
Okay, we can do that with a small VBA routine.

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


»bp
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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.
0
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)
        Else
            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


»bp
0
Troy OverholtAuthor Commented:
Thank you so much!
0
Bill PrewCommented:
Welcome.


»bp
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.