Create text string from input

[ihorwitz]

Is there a formula that creates a text string as shown below from the following inputs:

Period Start 4
Period End 6
Factor 0.5
Text string 4|0.5;5|0.5;6|0.5

The text string is in a format that a software program requires for an upload file.

Any help appreciated.

Thanks

 

[gaz_chops]

Think you need to provide more info. if you want a response.

 

[ihorwitz]

gaz_chops

Perhaps if I present it differently in a spreadsheet format

A B C D
Period Start Period End Factor Text String
4 6 0.5 4|0.5;5|0.5;6|0.5

The text string needs to return each individual period in the range 4 through 6 an concatenate a "|" together with the factor and ";" after each individual period.

Hopefully this helps

 

[Eric W]

If you have Excel 365 with the TEXTJOIN function:

=TEXTJOIN(";",TRUE,ROW(INDIRECT(A2&":"&B2))&"|"&C2)

confirmed with Control+Shift+Enter. If you don't have TEXTJOIN, you'll probably need a UDF.

 

[Rick Rothstein]

If you don't have TEXTJOIN, you'll probably need a UDF.

This would be one way to write such a UDF...

[table="width: 500"]
[tr]
	[td]Function DateFactor(PeriodStart As Long, PeriodEnd As Long, Factor As Double) As String
  DateFactor = Join(Evaluate("TRANSPOSE(ROW(" & PeriodStart & ":" & PeriodEnd & "))"), "|" & Factor & ";") & "|" & Factor
End Function[/td]
[/tr]
[/table]

The function takes three arguments... the first is the period start number, the second is the period end number and the third is the factor. So, for you example, the formula to put in cell D2 would be this...

=DateFactor(A2,B2,C2)


HOW TO INSTALL UDFs
------------------------------------
If you are new to UDFs, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the above code into the code window that just opened up. That's it.... you are done. You can now use DateFactor just like it was a built-in Excel function. For example,

=DateFactor(A2,B2,C2)

If you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm) and answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.