SOLVED
Home

Create text string from input

%3CLINGO-SUB%20id%3D%22lingo-sub-299819%22%20slang%3D%22en-US%22%3ECreate%20text%20string%20from%20input%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-299819%22%20slang%3D%22en-US%22%3E%3CP%3EIs%20there%20a%20formula%20that%20creates%20a%20text%20string%20as%20shown%20below%20from%20the%20following%20inputs%3A%3CBR%20%2F%3E%3CBR%20%2F%3EPeriod%20Start%204%3CBR%20%2F%3EPeriod%20End%206%3CBR%20%2F%3EFactor%200.5%3CBR%20%2F%3EText%20string%204%7C0.5%3B5%7C0.5%3B6%7C0.5%3CBR%20%2F%3E%3CBR%20%2F%3EThe%20text%20string%20is%20in%20a%20format%20that%20a%20software%20program%20requires%20for%20an%20upload%20file.%3CBR%20%2F%3E%3CBR%20%2F%3EAny%20help%20appreciated.%3CBR%20%2F%3E%3CBR%20%2F%3EThanks%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-299819%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-300757%22%20slang%3D%22en-US%22%3ERe%3A%20Create%20text%20string%20from%20input%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-300757%22%20slang%3D%22en-US%22%3EAlas%2C%20I%20do%20not%2C%20I%20tend%20to%20jump%20right%20to%20a%20VBA%20solution%2C%20usually%20a%20lot%20easier%20to%20read%2Fwork%20with%20for%20me%20than%20a%20bunch%20of%20nested%20built%20in%20functions.%20Sorry.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-300730%22%20slang%3D%22en-US%22%3ERe%3A%20Create%20text%20string%20from%20input%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-300730%22%20slang%3D%22en-US%22%3E%3CP%3EThanks%2C%20this%20works%20great.%26nbsp%3B%20If%20you%20do%20have%20a%20formula%20it%20would%20be%20even%20better.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-300680%22%20slang%3D%22en-US%22%3ERe%3A%20Create%20text%20string%20from%20input%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-300680%22%20slang%3D%22en-US%22%3EThe%20UDF%20I%20suggested%20would%20work%20in%20'16.%20Textjoin%20is%20office%20365%20only.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-300678%22%20slang%3D%22en-US%22%3ERe%3A%20Create%20text%20string%20from%20input%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-300678%22%20slang%3D%22en-US%22%3E%3CP%3EUnfortunately%20my%202016%20Excel%20version%20has%20not%20got%20the%26nbsp%3BTEXTJOIN%20function%20-%20is%20there%20an%20alternative%20to%20this%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-300511%22%20slang%3D%22en-US%22%3ERe%3A%20Create%20text%20string%20from%20input%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-300511%22%20slang%3D%22en-US%22%3E%3CP%3EHi%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%3E%7B%3DTEXTJOIN(%22%3B%22%2CTRUE%2CROW(INDIRECT(A2%26amp%3B%22%3A%22%26amp%3BB2))%26amp%3B%22%7C%22%26amp%3BC2)%7D%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-300431%22%20slang%3D%22en-US%22%3ERe%3A%20Create%20text%20string%20from%20input%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-300431%22%20slang%3D%22en-US%22%3E%3CP%3EAre%20you%20familiar%20with%20User%20Defined%20Functions%3F%20I%20believe%20the%20function%20below%20would%20do%20what%20you%20are%20after.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EFunction%20PeriodString(PeriodStart%20As%20Integer%2C%20PeriodEnd%20As%20Integer%2C%20Factor%20As%20String)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EDim%20i%20As%20Integer%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EFor%20i%20%3D%20PeriodStart%20To%20PeriodEnd%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20i%20%3D%20PeriodEnd%20Then%3CBR%20%2F%3EPeriodString%20%3D%20PeriodString%20%26amp%3B%20i%20%26amp%3B%20%22%7C%22%20%26amp%3B%20Factor%3CBR%20%2F%3EElse%3C%2FP%3E%3CP%3EPeriodString%20%3D%20PeriodString%20%26amp%3B%20i%20%26amp%3B%20%22%7C%22%20%26amp%3B%20Factor%20%26amp%3B%20%22%3B%22%3C%2FP%3E%3CP%3EEnd%20If%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENext%20i%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EEnd%20Function%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-300147%22%20slang%3D%22en-US%22%3ERe%3A%20Create%20text%20string%20from%20input%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-300147%22%20slang%3D%22en-US%22%3E%3CP%3EIn%20case%20this%20is%20not%20clear%20perhaps%26nbsp%3Bif%20I%20present%20it%20in%20a%20spreadsheet%20format%3CBR%20%2F%3E%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20A%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20%26nbsp%3B%20B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20%26nbsp%3BC%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20D%3CBR%20%2F%3EPeriod%20Start%26nbsp%3B%26nbsp%3B%26nbsp%3B%20Period%20End%26nbsp%3B%26nbsp%3B%26nbsp%3B%20Factor%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20%26nbsp%3BText%20String%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%204%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%206%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%200.5%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%204%7C0.5%3B5%7C0.5%3B6%7C0.5%3CBR%20%2F%3E%3CBR%20%2F%3EThe%20text%20string%20needs%20to%20return%20each%20individual%20period%20in%20the%20range%204%20through%206%20and%20concatenate%20a%20%22%7C%22%20together%20with%20the%20factor%20and%20%22%3B%22%20after%20each%20individual%20period.%3CBR%20%2F%3E%3CBR%20%2F%3EHopefully%20this%20helps%3C%2FP%3E%3C%2FLINGO-BODY%3E
JetJungle
New 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

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.