Forum Discussion

Copper Contributor

Dec 12, 2018

Solved

Create text string from input

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

Formulas and Functions

JWR1138
Dec 12, 2018
Are 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

JetJungle

Copper Contributor

Dec 12, 2018

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

Detlef_Lewin
Silver Contributor
Dec 12, 2018
Hi

{=TEXTJOIN(";",TRUE,ROW(INDIRECT(A2&":"&B2))&"|"&C2)}
- JetJungle
  Copper Contributor
  Dec 12, 2018
  Unfortunately my 2016 Excel version has not got the TEXTJOIN function - is there an alternative to this?
  - JWR1138
    Iron Contributor
    Dec 12, 2018
    The UDF I suggested would work in '16. Textjoin is office 365 only.
JWR1138
Iron Contributor
Dec 12, 2018
Are 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
- JetJungle
  Copper Contributor
  Dec 12, 2018
  Thanks, this works great. If you do have a formula it would be even better.
  - JWR1138
    Iron Contributor
    Dec 12, 2018
    Alas, I do not, I tend to jump right to a VBA solution, usually a lot easier to read/work with for me than a bunch of nested built in functions. Sorry.