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: