concatenating and formatting dynamic number of strings

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:

2.3008/1/2018
2.4009/1/2018
2.85010/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
Joined
Jun 12, 2014
Messages
57,030
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Mar 30, 2017
Messages
34
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
Joined
Jun 12, 2014
Messages
57,030
Office Version
  1. 365
Platform
  1. Windows
How about
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)
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
57,030
Office Version
  1. 365
Platform
  1. Windows
Glad to help & thanks for the feedback
 

Watch MrExcel Video

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top