Find and Replace Carriage Returns to carry out text to column

    Hi All,


    Really struggling with this one!


    I have a data set from a client that has allergen information in one of the columns. For example Contains Milk, May Contain Wheat etc etc. The issue is that if a product contains more than one allergen they have been listed in the same cell but what looks like a new line within the same cell. I eventually need to break these out into 14 columns (one column for each of the 14 major allergens) Im planning to do a delimited text to column once I can get a delimiter character in place, thats where I fall down!


    I have attached a sample from my extract in hope it would aid someone to be able to offer some guidance.


    forum.ozgrid.com/index.php?attachment/64054/

    Re: Find and Replace Carriage Returns to carry out text to column


    Try this little piece of code to see what characters you are really dealing with.

    Re: Find and Replace Carriage Returns to carry out text to column


    This will do the trick:


    Re: Find and Replace Carriage Returns to carry out text to column


    No vba needed.


    Select col.A - Data - TextToColumns - Dlimited
    Other: enter Alt + 0010
    0010 must be entered from number pad


    Re: Find and Replace Carriage Returns to carry out text to column


    I've edited my original code with 3 things:


    1) The delimiter is a Line Feed, not a Carriage Return (which is the point that NoSparks was getting at)
    2) As we're moving the array across columns there is no need to transpose, this is why only the first item was being repeated (my bad)
    3) Ignore blank cells


    Try that and you should be golden ;)


    Or use jindon's solution (always use jindon's solution, it's guaranteed to be better)

    Re: Find and Replace Carriage Returns to carry out text to column


    Hi SO, Am I missing something here??? I've copied your code above into a new module, but when I run it nothing happens?


    See attachment, sure I'm being stupid!!!



    jindon - Ive tried that but it won't accept the 0010, maybe as I'm on excel for mac??

    Re: Find and Replace Carriage Returns to carry out text to column


    Ahh, Mac.. It will not work.


    How about

    Code
    Sub test()
    Dim r As Range, x
    For Each r In Range("a2", Range("a" & Rows.Count).End(xlUp))
        If r.Value <> "" Then
            x = Split(r.Value, vbNewLine)
            r(, 2).Resize(, UBound(x) + 1).Value = x
        End If
    Next
End Sub

    Re: Find and Replace Carriage Returns to carry out text to column


    MASSIVE THANK YOU!!!


    Works a treat! Thank you very much thats hours and hours of saved time for me!!!!!!

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login