Excel - Find and Replace from rows to separation by commas

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I would like to take information that is one column but over 2000 rows and
move it into the A1 cell, but separated by commas. So instead of separation
by rows, make it separation by commas. How do I do this?
 
You can do this by macro

Sub AddCommas()
Set DataRange = Application.InputBox("Select data", , , , , , , 8)
Set TargetCell = Application.InputBox("Select target cell", , , , , ,
8)
For Each Cell In DataRange
TargetString = TargetString & "," & Cell.Value
Next
TargetString = Right(TargetString, Len(TargetString) - 1)
TargetCell.Value = TargetString
End Sub


Marti
 
This can also quickly be done via Word. Copy to Word, convert table to text,
and use Edit, Replace in Word to replace paragraph mark with comma. Copy to
Excel.
 
In cell B1 put

=A1&","&B2

and formula copy that to the end of your data

then take cell B2, copy, and paste special = Values into A1

delete all other rows and column B

--
 
This is the one! This was super easy. I kept thinking that I know I can
choose characters from a list with find and replace and I had forgotten that
I can do it in Word! Thanks!
 
Back
Top