Split a string into chunks of 35 or less at spaces

[Amapola188]

Good Afternoon

Here's a juicy problem that I can't find a solution to. I've seen iterations of it and someone found a solution in Excel but I can't find how to do it Power Automate.

The problem arises because my data goes into SAP and there's a character limit of 35 for each field.

So I have a customer name which can exceed 35 and if that's the case, I want to chunk the name at a space. (for a moment I thought the new chunk function could do it but it chunks it just into even-length strings).

I'm thinking I need to find the space at the position closest to 34 (but less than ..) and split the string there. But how do I tell it to do that. And it sounds like a lot of Apply to Each looping and I'm already inside a loop.

Thanks for any suggestions.

Christine

[NicholasNg]

I'm thinking that what you could do is an apply to each loop. First outside of your loop you create 2 string variables.

1. First variable is for working on working out whether the name is =<35 characters.
2. Second variable is for saving the list of chunks that are =< 35 characters.

Next, within your loop, you split by " " (space) so that each word becomes part of the array. Create a new apply to each step based on this array. You append it to the first string variable. After that step do a condition to check the length of the first string variable is less than or equal to 35. 

•  If less than 35, you can leave it blank.
• If more than 35, you have 2 steps. First append your second string variable (make sure you add a delimiter that you want to use, you can split it by that delimiter later if you want it in an array format, alternatively you can just make this an array variable and append straight) with the first string variable but do a replace function to replace the current word within the first string with ''. Second, Set your first string variable to the current item. 
• After the apply to each is over. Append the first string variable to the second string variable array so that you don't lose the last item that is less than 35 characters.

Based on the fact that you said this is within a loop, at the end of the external loop, set both variables to null to empty out the variables for the next loop. 

That's what I'm thinking of at least. I haven't tested but that's how I would go about this problem if I had to do it myself. 

[creativeopinion · Accepted solution]

@Amapola188 I've created a manual trigger flow with Compose actions to demonstrate.

Get the Length of the Original Text String

I used a Compose action to store a text string. 

Add another Compose action after it to store the length of your text string. Insert and expression and use the length() function.

Click the dynamic content tab and insert the outputs (or dynamic content) that contains your original text string. I'm using the outputs from the Compose action above. 

Compose a New Text String

You'll need to insert an expression to compose the new text string. You will need to replace the content i've indicated with square brackets (the square brackets will also need to be removed. 

if(
greater([output from Compose action storing original text string length], 34), 
substring([original text string], 0, lastIndexOf(substring([original text string], 0, 35), ' ')),
[original text string]
)

Explanation below. 

if(
greater(length([output from Compose action storing original text string length]), 34), // Check if string length is greater than 34
substring(
[original text string], 
0, 
lastIndexOf(
substring([original text string], 0, 35), // Get the first 35 characters of the string
' ' // Find the last space in the first 35 characters
)
), // If greater than 34 characters, split at last space before 35th character
[original text string] // If 34 characters or less, return the whole string
)

Count New Text String

Add another Compose action (optional) to count the new text string length.

Run a test!

Hope this helps!

If I helped you solve your problem—please mark my post as a solution ✅.
Consider giving me a 👍 if you liked my response!

👉Watch my tutorials on YouTube
👉Tips and Tricks on TikTok

[Amapola188]

@creativeopinion I love it. I was quite prepared to work with a condition but this does it in one expression.

PS - anybody who wants to use this, there's one bracket too many in the code. Remove the bracket in red.

greater([output from Compose action storing original text string length]), 34)

[creativeopinion]

No worries!

Sorry about that extra bracket. I was too quick with replacing the original references to the dynamic content in the expression. I've updated it below! 

[Amapola188]

I just wanted to add ... obviously we now also have to deal with the remainder of the string.

And I've done this ... I think strictly speaking, I should start the substring at lstIndexOf +1.

if(
greater(length(outputs('Compose_Customer_Proper_Case')), 34), 
substring(outputs('Compose_Customer_Proper_Case'),lastIndexOf(substring(outputs('Compose_Customer_Proper_Case'), 0, 35), ' '),length(outputs('Compose_Customer_Proper_Case'))),
outputs('Compose_Customer_Proper_Case')
)

@creativeopinion I didn't know about lastIndexOf ... it's going to be so useful! 😁