Add SemiColon to each value (each line) in a cell

Question

Hello All,

i have the following values in a single cell let be A1

1234
567
454

Likewise all the A(N) are filled with values. N various from 1000 to 1500

i want this to get converted as

1234;567;454

Any shortcut available?

Answer 1

Edit: Sorry, had not read your questions properly...

You could write a vba-script like that:

Sub test()
  Dim result As String
  result = Replace(ActiveCell.value, Chr(10), ";")
  ActiveCell.Offset(1, 0).Select
  ActiveCell.value = result
End Sub

It will take the active cell, replace all newlines by semicolons and put the result in the next line.

Edit: Another version doing this for multiple cells:

Sub test()
  Dim value As String
  Do
    value = ActiveCell.value
    If (value = "") Then Exit Do
    ActiveCell.Offset(0, 1).value = Replace(ActiveCell.value, Chr(10), ";")
    ActiveCell.Offset(1, 0).Select
  Loop While (True)
End Sub

This version will start at the active cell, and loop through all cell below until it finds an empty cell.

The replaced value is written into the cell next to the original one. If you want to replace the original value, remove .Offset(0, 1).

The second parameter is the value to be replaced, it's Chr(10), the Newline character in our case.