Dec 12 2018 08:52 AM
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
Dec 12 2018 09:18 AM
In case this is not clear perhaps if I present it 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 and concatenate a "|" together with the factor and ";" after each individual period.
Hopefully this helps
Dec 12 2018 09:42 AM
SolutionAre you familiar with User Defined Functions? I believe the function below would do what you are after.
Function PeriodString(PeriodStart As Integer, PeriodEnd As Integer, Factor As String)
Dim i As Integer
For i = PeriodStart To PeriodEnd
If i = PeriodEnd Then
PeriodString = PeriodString & i & "|" & Factor
Else
PeriodString = PeriodString & i & "|" & Factor & ";"
End If
Next i
End Function
Dec 12 2018 09:49 AM
Hi
{=TEXTJOIN(";",TRUE,ROW(INDIRECT(A2&":"&B2))&"|"&C2)}
Dec 12 2018 10:18 AM
Unfortunately my 2016 Excel version has not got the TEXTJOIN function - is there an alternative to this?
Dec 12 2018 10:20 AM
Dec 12 2018 10:41 AM
Thanks, this works great. If you do have a formula it would be even better.
Dec 12 2018 10:57 AM
Dec 12 2018 09:42 AM
SolutionAre you familiar with User Defined Functions? I believe the function below would do what you are after.
Function PeriodString(PeriodStart As Integer, PeriodEnd As Integer, Factor As String)
Dim i As Integer
For i = PeriodStart To PeriodEnd
If i = PeriodEnd Then
PeriodString = PeriodString & i & "|" & Factor
Else
PeriodString = PeriodString & i & "|" & Factor & ";"
End If
Next i
End Function