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.

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.

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.