# "concatenate" type of problem?

#### pontiff

##### Board Regular
Hi,

I've got a row of cells containing numerical values, eg. row A1 to O1 contains 3 0 0 0 1 0 0 0 0 1 0 0 0 0 3

I need a formula which will summarise this to produce 3113 ( it must ignore the zeros).

Yours in hope,
Pontiff

### Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
A UDF in a standard module

Code:
``````Function concat(r As Range) As String
Dim c As Range
For Each c In r
If c.Value <> 0 Then concat = concat & c.Value
Next c
End Function``````

Excel Workbook
ABCDEFGHIJKLMNOP
13000100001000033113
Sheet5

Perhaps
=SUBSTITUTE(SUMPRODUCT(A1:O1, 10^(COLUMNS(A1:O1)-COLUMN(A1:O1))), "0", "")+0

That worked a treat!!

Is it possible to put a space in between each number?......... Thanks very much.

You could format the cell # # # # # # # # # # # # # #

Try

Code:
``````Function concat(r As Range) As String
Dim c As Range
For Each c In r
If c.Value <> 0 Then concat = concat & c.Value & " "
Next c
concat = Left(concat, Len(concat) - 1)
End Function``````

Try

Code:
``````Function concat(r As Range) As String
Dim c As Range
For Each c In r
If c.Value <> 0 Then concat = concat & c.Value & " "
Next c
concat = Left(concat, Len(concat) - 1)
End Function``````

Fantastic thank you!! Here is a different UDF (loopless, one-liner) for you to consider...

Code:
``````Function ConCat(R As Range) As String
ConCat = Application.Trim(Replace(" " & Application.Trim(Join(Application.Index(R.Value, 1, 0), " ")), " 0", " "))
End Function``````
Or, as a more compact looking multi-liner (still loopless)...

Code:
``````Function ConCat(R As Range) As String
With Application
ConCat = .Trim(Replace(" " & .Trim(Join(.Index(R.Value, 1, 0), " ")), " 0", " "))
End With
End Function``````

Last edited:
Here is a different UDF (loopless, one-liner) for you to consider...

Code:
``````Function ConCat(R As Range) As String
ConCat = Application.Trim(Replace(" " & Application.Trim(Join(Application.Index(R.Value, 1, 0), " ")), " 0", " "))
End Function``````
Or, as a more compact looking multi-liner (still loopless)...

Code:
``````Function ConCat(R As Range) As String
With Application
ConCat = .Trim(Replace(" " & .Trim(Join(.Index(R.Value, 1, 0), " ")), " 0", " "))
End With
End Function``````

Cheers, does this leave a gap between the numbers and if so, how can I increase it ( say to 2 spaces?)

Cheers, does this leave a gap between the numbers and if so, how can I increase it ( say to 2 spaces?)

Yes, the code I posted leaves a singles space between the non-zero digits (what you asked for). Here is my multi-line UDF (chosen because it's "cleaner" looking) modified so you can set the number of spaces as part of the function call. To do that, I added a second argument to the function. Simply pass in any non-negative value (0 to whatever) and that number of spaces will be placed between your digits. So, for you current request, you would call the following code this way...

=ConCat(A1:O1,2)

for your previous request, this way...

=ConCat(A1:O1,1)

and for your original request, this way...

=ConCat(A1:O1,0)

Here is the code to use to do that...

Code:
``````Function ConCat(R As Range, SpaceCount As Long) As String
With Application
ConCat = Replace(.Trim(Replace(" " & .Trim(Join(.Index(R.Value, 1, 0), " ")), " 0", " ")), " ", Space(SpaceCount))
End With
End Function``````

Replies
10
Views
249
Replies
1
Views
173
Replies
20
Views
686
Replies
6
Views
199
Replies
1
Views
75

### Forum statistics

1,206,755
Messages
6,074,757
Members
446,084
Latest member
WalmitAal ### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?    1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option. Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com". Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button. Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button. Go back