PDA

View Full Version : Solved: Add commas on each line into cells



parscon
11-20-2012, 06:53 AM
I have a some data on my excel file .

Now I want to add a comma to first of each line on cell and after that remove the lines and all data an each cell will be in 1 line on that cell

if I have data on cell A1 like this

DAT-565912
Replaced No
20405503
11700352
ZM2906923

I want A1 to be (added comma and removed the line break )

DAT-565912,Replaced No,20405503,11700352,ZM2906923

Thank you

GarysStudent
11-20-2012, 07:21 AM
Give this a try:

Sub qwerty()
Dim s As String, v As Variant, r As Range
s = Chr(10)
For Each r In ActiveSheet.UsedRange
v = r.Value
If InStr(v, s) > 0 Then
r.Value = Replace(v, s, ", ")
End If
Next
End Sub

parscon
11-20-2012, 10:42 AM
Thank you very much , it is work . Thank you again .

shrivallabha
11-21-2012, 05:23 AM
It can be done through FIND and REPLACE box as well.
1] Press CTRL + H
2] Select the Range concerned
3] Find what: CTRL + J equivalent to line breaj
4] Replace with: , i.e. comma
5] Hit Replace all...