#### jbenfleming

##### New Member

- Joined
- Mar 30, 2017

- Messages
- 34

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:

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

2.300 | 8/1/2018 |

2.400 | 9/1/2018 |

2.850 | 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: