Find and replace slashes with new linfeed in Excel 2010

Question

Can anyone tell me how to replace a slash (/) with a linefeed in Excel 2010? I have a very long worksheet with words, origins, and definitions. In the origins column, some rows show multiple places seperated by slashes. I want each item in the cell to be on a new line within the cell. Is this possible?

Right now the cell looks like this

Roman/ Celtic/ Baltic

I want the cell to look like this

Roman
Celtic
Baltic

Answer 1

• select your cells
• right click, Format Cells ... goto the Alignment tab,and check Wrap Text (Pic 1)
• press ctrlf together
• goto the Replace tab
• under Find What enter "/"
• goto Replace With then press alt0010
• Replace All (Pic 2)

Pic 1

Pic 2

Answer 2

As another option, here's some VBA code to make a new function that will convert / to LF:

Public Function SlashToLF(oldstring As String) As String
    Dim newString As String
    Dim c As Long

    For c = 1 To Len(oldstring)
        If Mid(oldstring, c, 1) = "/" Then
            newString = newString & vbNewLine
        Else
            newString = newString & Mid(oldstring, c, 1)
        End If
    Next
    slashtolf = newString

End Function

If you put the formula =SlashToLF(A1) in another cell you'll get the new version with linefeeds. You can then Copy/Paste As Values over your original if you like. Also, you will need to format the cell as Wrap Text for it to work.