# concatenating and formatting dynamic number of strings

#### jbenfleming

##### New Member
So here is what I'm trying to accomplish. There is a number out to three decimals in column A. Date in column B (mm/dd/yyyy). Say I have this data:

 2.3 8/1/2018 2.4 9/1/2018 2.85 10/1/2018

<tbody>
</tbody>

I want to concatenate this strings to where I get a single string of "2.33 until 08/01/2018, 2.400 until 09/01/2018, 2.850 until 10/01/2018"

I have accomplished this with the below formula. But as you can see it is quite messy. I will end up having this formula be able to work with any number of rows between 1 and 30, inclusive.

=LEFT(CONCATENATE(IF(ISNUMBER(A1),FIXED(A1,3) & " until " & TEXT(B1,"mm/dd/yyyy") & ", ",""),IF(ISNUMBER(A2),FIXED(A2,3) & " until " & TEXT(B2,"mm/dd/yyyy") & ", ",""),IF(ISNUMBER(A3),FIXED(A3,3) & " until " & TEXT(B3,"mm/dd/yyyy") & ", ","")),LEN(CONCATENATE(IF(ISNUMBER(A1),FIXED(A1,3) & " until " & TEXT(B1,"mm/dd/yyyy") & ", ",""),IF(ISNUMBER(A2),FIXED(A2,3) & " until " & TEXT(B2,"mm/dd/yyyy") & ", ",""),IF(ISNUMBER(A3),FIXED(A3,3) & " until " & TEXT(B3,"mm/dd/yyyy") & ", ","")))-2)

I know, it's pretty ridiculous. Wondering if any of you fine folks have a simpler solution in mind. Thanks and have a great day.

Last edited:

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

#### Fluff

##### MrExcel MVP, Moderator
At the moment you are adding ", " to the end of the string & then removing it. So you can simplify it slightly like
=CONCATENATE(IF(ISNUMBER(A1),FIXED(A1,3) & " until " & TEXT(B1,"mm/dd/yyyy") & ", ",""),IF(ISNUMBER(A2),FIXED(A2,3) & " until " & TEXT(B2,"mm/dd/yyyy") & ", ",""),IF(ISNUMBER(A3),FIXED(A3,3) & " until " & TEXT(B3,"mm/dd/yyyy"),""))

Do you need to keep this as formulae only, or would you be interested in a UDF, or normal macro?

#### jbenfleming

##### New Member
Only problem is that if i were to use this formula for say, 3 rows, and then delete the third row. It would update but leave a comma on the end. That's why I wrap the thing with that =left. I'd rather avoid a macro, it would be easy to write but this is for a coworker and I always try to avoid macros when helping others. Didn't think about making a UDF though. Not sure where to start with that.

#### Fluff

##### MrExcel MVP, Moderator
Code:
``````Function CreateText(Rng As Range) As String
Dim Cl As Range
For Each Cl In Rng
If IsNumeric(Cl.Value) And Cl <> "" Then
If CreateText = "" Then
CreateText = Application.Fixed(Cl, 3) & " until " & format(Cl.Offset(, 1), "mm/dd/yyyy")
Else
CreateText = CreateText & ", " & Application.Fixed(Cl, 3) & " until " & format(Cl.Offset(, 1), "mm/dd/yyyy")
End If
End If
Next Cl
End Function``````
Used like
=CreateText(A1:A3)

#### jbenfleming

##### New Member
This is perfect. Thank you so much!

#### Fluff

##### MrExcel MVP, Moderator
Glad to help & thanks for the feedback

Replies
6
Views
71
Replies
4
Views
308
Replies
12
Views
415
Replies
1
Views
205
Replies
1
Views
111

1,130,439
Messages
5,642,126
Members
417,257
Latest member
Sarahbw

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