Hi,
In excel I have a list of invoice numbers, one on each cells (multiple rows)

43333,
43332,
12323,
78922,

I need a formula that will put all those in a single line/cell and with a single quote in between, so like this:

'43333','43332','12323','78922',

Any help would be appreciated. Thanks!

Hi There,

Not sure if there is any excel built in function that can do this for you (Again as per my knowledge!). Here is a small VBA function that may help you.

Public Function ConCat(Rng As Range, EncloseWith As String, ParseWith As String)
Dim cell As Range
Dim Temp As String
For Each cell In Rng.Cells
Temp = Temp & EncloseWith & cell & EncloseWith & ParseWith
Next cell
ConCat = Temp
End Function

The arguments are as following
Rng - select the range which will be concatenated
EncloseWith - A string which will enclose each cell value. ' in your example.
ParseWith - A string that will separate each entry. , in your example.
While using the function just keep numbers in each cell (without commas).
Another small correction that you will have to make is to remove the last comma which may not be required.

I understand this isn't the best solution but might be useful if you haven't been able to find a better one.

Let me know if this helps.

Thanks,
Prashant

On Wednesday, October 17, 2012 1:33:44 PM UTC-5, JAlvarez wrote:
Hi,

In excel I have a list of invoice numbers, one on each cells (multiple

rows)



43333,

43332,

12323,

78922,



I need a formula that will put all those in a single line/cell and with

a single quote in between, so like this:



'43333','43332','12323','78922',



Any help would be appreciated. Thanks!









--

JAlvarez

You don't say how many rows to put in a single cell (or different columns on the same row)

JAlvarez,

The macro below will prompt you for the data location. The first prompt will provide the range of values to combine. The default value is whatever cells are selected, but you can change it to any range of cells that you wish to combine.

Then, the macro will build a string where the contents of each cell will be enclosed in single-quotes and separated by commas. Before pasting the data to your sheet, the macro will display a message box with the string. If it looks right, click Yes and the macro will prompt you for a destination range (default is C1, but you can change that in the code below) and pastes the value there.

Hope this helps,

Ben

Code:

Sub ConcatIt()
Dim rValues As Range
Dim sText As String
Dim c As Range

On Error Resume Next
Set rValues = Application.InputBox("Please select the range of cells to combine.", _
"Value Range?", Selection.Address, , , , , 8)
On Error GoTo 0
If rValues Is Nothing Then Exit Sub

For Each c In rValues
sText = sText & Chr(39) & c.Value & Chr(39) & ", "
Next c
sText = Left(sText, Len(sText) - 2)

If MsgBox("Your result is: " & vbCr & vbCr & sText & vbCr & vbCr & _
"Would you like to store this value in a cell?", vbInformation + vbYesNo) _
= vbYes Then
On Error Resume Next
Set rValues = Nothing
Set rValues = Application.InputBox("Please select a destination cell", "Destination?", _
Sheet1.Range("C1").Address, , , , , 8)
On Error GoTo 0
If rValues Is Nothing Then
Exit Sub
Else
rValues.Value = Chr(39) & sText
End If
End If

End Sub