how do i insert line break in multiple cells???

sara2009

New Member
Joined
Apr 1, 2013
Messages
16
hello everyone,,please helpp meee,,i have alott of data in my cells written like this sara,thomas ....i want to insert a line break so it appears like
Sara,
thomas
how do i do this for multiple cells??? data is in row 2 to row 439 in column 4.
 

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.
Sara you would edit the cell (Press F2), move the position where you want the line break and use Alt + Enter. Sorry to say you can't do this on mass.
 
Upvote 0
Try

Code:
Sub test()
Dim c As Range
For Each c In Range("D2:D439")
    c.Value = Replace(c.Value, ",", Chr(10))
Next c
End Sub
 
Upvote 0
Without going into code, you can search and replace; first select the cells you want to effect a change in (you should select more than one cell as if you don't, the whole sheet is searched and replaced!), then press Ctrl + H to bring up the dialogue box, then in the find what field enter a comma, then in the Replace with field you need to enter a new line character; this is where you need to be careful. Essentially, you need to enter that character with the numeric keypad while holding down the ALT key, type in: zero one zero:
010
then let go of the alt key. You won't see much in the field, it doesn't matter, click OK and you're done.

Has your keyboard not got a numeric keypad? Laptops/notebooks often don't seem to have one, but they normally do, usually reachable by a NumLock key, when you then need to look for the numbers on the keyboard - on mine the M has a 0 and the J has a 1
 
Upvote 0
thx mr.excelmvp for ur help,, i tried this code but it appeared like this
Sara</SPAN>
Thomas

i need it to appear like this with a comma afta Sara and there shud not be space on a new line before Thomas. please help,,,

Sara,
Thomas
</SPAN>
 
Upvote 0
Try

Code:
Sub test()
Dim c As Range
For Each c In Range("D2:D439")
    c.Value = Replace(c.Value, " ", Chr(10))
Next c
End Sub
 
Upvote 0
Peter is assuming you have a space after the comma, but your original post does not show that... is there a space after the comma or not?

If not, try this non-looping one-liner macro...
Code:
Sub SplitAtComma()
  Range("D2:D439") = Evaluate("IF(LEN(D2:D439),SUBSTITUTE(D2:D439,"","","",""&CHAR(10)),"""")")
End Sub
On the other hand, if there is (or could be) a space after the comma, then try this one instead...
Code:
Sub SplitAtComma()
  Range("D2:D439") = Evaluate("IF(LEN(D2:D439),SUBSTITUTE(SUBSTITUTE(D2:D439,"", "","",""),"","","",""&CHAR(10)),"""")")
End Sub
 
Upvote 0

Forum statistics

Threads
1,213,554
Messages
6,114,280
Members
448,562
Latest member
Flashbond

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top