• 469K Members
• 5,015 Online
• 568K Conversations
SOLVED

New Contributor

# 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 that a software program requires for an upload file.

Any help appreciated.

Thanks

7 Replies

# Re: Create text string from input

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

Solution

# Re: Create text string from input

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

# Re: Create text string from input

Hi

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

# Re: Create text string from input

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

# Re: Create text string from input

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

# Re: Create text string from input

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

# Re: Create text string from input

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.
Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
46 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
30 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
13 Replies