Concatenate a range of cells with delimiter
 

Hi, I would like to concatenate a long range of cells using a semicolon to
separate each number. I have come across several macros online that will do
this, but none have included a delimiter in the code and, unfortunately, I
don't know enough about VBA to make any modifications. Does anyone know of a
way to do this? Thanks in advance for any information.

Concatenate a range of cells with delimiter
 

FJ:

Try this, I've set an optional sperator so that you can specify another
sperator.

Function concat(rRange As Range, Optional szSeperator As String = ";")

Dim szAns As String
Dim rCell As Range

On Error GoTo error_line

For Each rCell In rRange
szAns = szAns & rCell.Value & szSeperator
Next rCell

concat = Left(szAns, Len(szAns) - Len(szSeperator))

error_line:

End Function

--
Hope this helps
Martin Fishlock
Please do not forget to rate this reply.


"FJ" wrote:

Hi, I would like to concatenate a long range of cells using a semicolon to
separate each number. I have come across several macros online that will do
this, but none have included a delimiter in the code and, unfortunately, I
don't know enough about VBA to make any modifications. Does anyone know of a
way to do this? Thanks in advance for any information.

Concatenate a range of cells with delimiter
 

Hi, Martin, thank you so much! :) It's just what I needed! I really
appreciate it! :)



"Martin Fishlock" wrote:

FJ:

Try this, I've set an optional sperator so that you can specify another
sperator.

Function concat(rRange As Range, Optional szSeperator As String = ";")

Dim szAns As String
Dim rCell As Range

On Error GoTo error_line

For Each rCell In rRange
szAns = szAns & rCell.Value & szSeperator
Next rCell

concat = Left(szAns, Len(szAns) - Len(szSeperator))

error_line:

End Function

--
Hope this helps
Martin Fishlock
Please do not forget to rate this reply.


"FJ" wrote:

Hi, I would like to concatenate a long range of cells using a semicolon to
separate each number. I have come across several macros online that will do
this, but none have included a delimiter in the code and, unfortunately, I
don't know enough about VBA to make any modifications. Does anyone know of a
way to do this? Thanks in advance for any information.