formula to format text with single quotes

[JAlvarez]

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

43333,
43332,
12323,
78922,

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

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

Any help would be appreciated. Thanks

 

[Don Guillett]

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!

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

 

[Ben McClave]

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 beenclosed 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