Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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

share|improve this question

migrated from stackoverflow.com Oct 5 '12 at 12:01

This question came from our site for professional and enthusiast programmers.

add comment

2 Answers 2

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

Pic 1 Pic 1

Pic 2 Pic 2

share|improve this answer
    
Thanks brettdj for the great example... unfortunately it does not work... it only removes the slashes... I have tried it several times and checked to make sure everything was set as you have shown... No Go... won't work for me... –  Jeff Spijkers Oct 5 '12 at 12:15
    
Use Alt+0010 instead (hold Alt all the way) –  nutsch Oct 5 '12 at 16:10
add comment

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.