SOLVED

Create text string from input

Copper Contributor

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

7 Replies

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

best response confirmed by JetJungle (Copper Contributor)
Solution

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

Hi

 

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

 

Unfortunately my 2016 Excel version has not got the TEXTJOIN function - is there an alternative to this?

The UDF I suggested would work in '16. Textjoin is office 365 only.

Thanks, this works great.  If you do have a formula it would be even better.

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.
1 best response

Accepted Solutions
best response confirmed by JetJungle (Copper Contributor)
Solution

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

View solution in original post