Split String by Spaces and extrapolate this string to the last space before a character limit

[AndyBeds1]

Hello all,

My current scenario is as follows;

I have a gallery with 4 text inputs. Each of these inputs have a 35 character limit. This is sent to an API call, where it may respond with a suggestion for the full value of text outputs.

If the suggested value is > 35, I need to gracefully split the string across these text inputs, by word, not by the raw position in the string. (I don't want to cut words in half just because they are character 35 and 36)

I have the following formula, which returns me a table with each space separated word, the position in the original string that the space was at, and the position in the original string that the space and the word end at. 

Formula: 

// Define the string
Set(varString, "Sunrise paints the sky with hues of gold and red");
Set(varStringLength, Len(varString));

ClearCollect(SplitString,
AddColumns(
    Split(varString, " "),
    "SpacePosition",
    If(
        ThisRecord.Value = First(Split(varString, " ")).Value,
        0,
        If(
            IsBlank(LookUp(Split(varString, " "), Value = ThisRecord.Value, Value)),
            Find(ThisRecord.Value, varString) - 1,
            Find(ThisRecord.Value, varString, Find(LookUp(Split(varString, " "), Value = ThisRecord.Value, Value), varString) + 1) - 1
        )
    ),
    "EndPosition",
    If(
        ThisRecord.Value = First(Split(varString, " ")).Value,
        0,
        If(
            IsBlank(LookUp(Split(varString, " "), Value = ThisRecord.Value, Value)),
            Find(ThisRecord.Value, varString) - 1,
            Find(ThisRecord.Value, varString, Find(LookUp(Split(varString, " "), Value = ThisRecord.Value, Value), varString) + 1) - 1
        ))
     + Len(ThisRecord.Value)
)
);

For example:

Sunrise paints the sky with hues of gold and red is the suggested text. 

Rather than the result looking like;
Row 1:  Sunrise paints the sky with hues o
Row 2: f gold and red is the suggested

The result should be:

Row 1: Sunrise paints the sky with hues

Row 2: of gold and red is

How can I form the strings for each row? I am not opposed to abandoning my original code. 

[Chris-D]

Hi @AndyBeds1,

This should work 🙂 

// Use With() to define input parameters in our scope
With({
    String: TextInput3_1.Text,
    MaxLength: 35
},
    // Use With() here to add variables to scope
    With({
        Spaces: 

        // Distinct() removes duplicate values from a list
        Distinct( 

            // ForAll() loops through each character of the string
            ForAll( 

                // Sequence creates a list of numbers from 1 to MaxLength
                Sequence( MaxLength), 

                // Find() will tell us the next position of a space character
                Find( " ", String, Value)
            ), 

            // This will remove duplicate numbers
            // Leaving us with the index of each space character            
            Value
        )
    },
        // Left() takes the left characters from a string
        Left( 
            String, 

            // Filter() returns the indexes of all spaces before our cut off point
            // Last() returns the last one
            Last( Filter( Spaces, Value <= MaxLength)).Value 
            
            // -1 because otherwise we get a trailling space character
            -1
        )
    )
)

Edit: 
My solution doesn't quite work. It works for single strings only, didn't fully read the question! Will work on it and get back to you soon.

[Aapok]

Since you're calculating the end position of each string to your table, we can use Concat together with filter as follows:

Concat(Filter(SplitString, EndPosition < 35), Value & " ");

Concat(Filter(SplitString, EndPosition >= 35 && EndPosition < 70), Value & " ")

etc..

I tested this with a simple label, without knowing more about your code this was as far as I could go.

Edit: Fixed off by one typo in code

[Chris-D · Accepted solution]

ok, I think I have it! My example uses a line length of 10 and makes a list of lines. 

Here's the code:

// Create a blank list to store text strings
ClearCollect( Lines, Blank());

// Use With() to define input parameters
With({
    // Trim() removes any surplus whitespace
    // Add a space to the end of the string to find later
    String: Trim(TextInput3_2.Text) & " ",
    LineLength: 10
},
    // Use With() to add variables to our scope
    With({

        // Int() rounds a number down to the nearest whole number
        NumLines: Int( Len(String) / LineLength) +1,

        // This part creates a list of the positions of the spaces
        SpacePositions: 

        // Distinct() with ForAll() removes duplicates
        Distinct( 
            ForAll( 
                // Sequence() and Len() loops over each character in the string
                Sequence( Len( String)), 

                // Find() checks for the next space character
                Find( " ", String, Value)
            ), 
            Value
        )
    },
        // ForAll() loops through each line
        ForAll( 
            Sequence( NumLines),
            With({
                // Work out current position based by adding the length of all previous lines
                // Sum() adds all numbers in a list, Len() gives us the line length
                CurrentPos: 1 +Sum( Lines, Len( Value))
            },
                Collect( 
                    Lines, 

                    // Mid() takes characters from the middle of a string
                    Mid( 
                        String, 

                        // Start position
                        CurrentPos, 

                        // Number of characters
                        // CurrentPos + LineLength is our maximum cut off point for this line
                        // Last() Filter() finds the last space before this point
                        // Take away start position to give the number of characters
                        Last( Filter( SpacePositions, Value <= CurrentPos + LineLength)).Value -CurrentPos
                    )
                )
            )
        )
    )
)

// Known issue:
// Fails if there are no spaces within the line length limit

Edit:

I think there's a better way which doesn't need to use a collection..... working on it!

EditEdit:

There isn't.

[AndyBeds1]

@Aapok and @Chris-D 

Both of these are great solutions - thank you! I have found both to work well for my scenario, and I was able to scale them up for my solutions. 

I also had the same issue with if there are no spaces within the line limit that this will fail, but I think that's just going to have to be an acceptable limitation.

I will accept one of the solutions but both were great, so thank you!

[Chris-D]

No problem 🙂 

Regarding the no spaces within line limit - I figured this is acceptable risk since, unless you're a chemist, it's very unlikely that you'll have a word longer than 35 characters. If this is an issue, I could fix this?