Formula to split up / break down long strings of text

Glen Urquhart

Hi, Hope you're all keeping well.

I was hoping someone could point me in the right direction of a formula / function that can help break down large sections of text. If someone could point me in the right direction, I should be able to work out the nuances and implementation myself.

I have an API that exports data into a single column. I need this information spread out over a number of different columns.

Screenshot of the Smartsheet below, and a highlighted version from word showing the individual columns required.

Any advice would be greatly appreciated.

Note: the start of the text extract required will always be a product code, i.e "K Pegs" or "WBS1(425)" etc from the example below. Although the contents of each item will not always be equal in length - It will need to extract data starting from one product code, and ending before the next product code.

Cheers all who have read!

MedaUser

I've recently tried outsmarting the formulas with using MID, LEFT, and RIGHT formulas for something like this, but was unable to figure it out. Unfortunately, I don't think any formula/functions exist at the moment in the Smartsheet formula library.

Glen Urquhart

Ah shame. I had considered those, however thought there may be some cell search / extract formula I was unaware of.

Thanks for your time!

MedaUser

It may be possible with one of their add-on integrations, but not in the core platform that I've found. Let me know if you discover a solution though!

Andrée Starå

Hi @Glen Urquhart

I hope you're well and safe!

I might have a solution, but could you share the text without the coloring and on separate rows instead?

Be safe and have a fantastic week!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

Glen Urquhart

Of course, thank you Andrée.

Please see below 2 examples of what the text may look like:

First export from API (broken into separate lines for each product)

K Pegs (Amount: 2.50 GBP, Quantity: 19, Color: Black RAL 9005),WBS1(425) Shelf unit with integrated peg rail (Amount: 202.00 GBP, Quantity: 5, Colour: Black RAL 9055, Length: 1200mm (8 pegs)),

WA(425) Wall and floor fixed bench with peg rails and shoe tray (Amount: 260.00 GBP, Quantity: 5, Colour: Dark Grey RAL 7043, Length: 1500mm (10 pegs), Height: Junior (400mm bench height)),

DRSC2 Door restrainer (Amount: 105.00 GBP, Quantity: 1, Colour: Green RAL 6005),

DRSC2 Door restrainer (polished stainless steel finish (Amount: 218.00 GBP, Quantity: 1),

N600(LT) Fixed Grab Rail (Amount: 28.00 GBP, Quantity: 1, Colour: White RAL 9010),GBP,2,708.50,

Array

Second export from API (broken into separate lines for each product)

K Pegs (Amount: 2.50 GBP, Quantity: 19, Color: Black RAL 9005),

WBS1(425) Shelf unit with integrated peg rail (Amount: 202.00 GBP, Quantity: 5, Colour: Black RAL 9055, Length: 1200mm (8 pegs)),

WA(425) Wall and floor fixed bench with peg rails and shoe tray (Amount: 260.00 GBP, Quantity: 5, Colour: Dark Grey RAL 7043, Length: 1500mm (10 pegs), Height: Junior (400mm bench height)),

DRSC2 Door restrainer (Amount: 105.00 GBP, Quantity: 1, Colour: Green RAL 6005),

DRSC2 Door restrainer (polished stainless steel finish (Amount: 218.00 GBP, Quantity: 1),

N600(LT) Fixed Grab Rail (Amount: 28.00 GBP, Quantity: 1, Colour: White RAL 9010),GBP,2,708.50,

Array

Cheers, Glen.

Glen Urquhart

Hi @Andrée Starå,

Hope you're well!

Did you have any luck in devising your work around on this?

Cheers!

Andrée Starå

@Glen Urquhart

Happy to help!

Thanks!

Yes, I have a starting point that you can continue to tweak, so it works for you.

It's pretty advanced, but I'll get back to the post with the solution. (It's a client solution I did a while ago, so it can probably be simplified)

Andrée Starå

@Glen Urquhart

Try something like this.

Column Name: Text (Where you paste the text)

Column Names: A, B, C, D, E, F (Where the formulas below would be used)

A

=LEFT(Text@row, FIND("),", Text@row) - 1)

B

=LEFT(SUBSTITUTE($Text@row, JOIN($A@row:A@row, "),") + "),", ""), FIND("),", SUBSTITUTE($Text@row, JOIN($A@row:A@row, "),") + "),", "")) - 1)

C (Usually, you'd drag the formula in B to all the other columns as needed, but because you don't have a delimiter in place that is always the same, you might need to tweak the formulas a little)

=LEFT(SUBSTITUTE($Text@row, JOIN($A@row:B@row, "),") + "),", ""), FIND(")),", SUBSTITUTE($Text@row, JOIN($A@row:B@row, "),") + "),", "")))

D

=LEFT(SUBSTITUTE($Text@row, JOIN($A@row:C@row, "),") + "),", ""), FIND("),", SUBSTITUTE($Text@row, JOIN($A@row:C@row, "),") + "),", "")) - 1)

E

=LEFT(SUBSTITUTE($Text@row, JOIN($A@row:D@row, "),") + "),", ""), FIND("),", SUBSTITUTE($Text@row, JOIN($A@row:D@row, "),") + "),", "")) - 1)

F

=LEFT(SUBSTITUTE($Text@row, JOIN($A@row:E@row, "),") + "),", ""), FIND("),", SUBSTITUTE($Text@row, JOIN($A@row:E@row, "),") + "),", "")) - 1)

Make sense?

Did that work/help?

✅Remember! Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up/Awesome or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

Shawn Church

@Andrée Starå

Using your formulas I am attempting to split some text that I JOINed using "|" as a delimiter. I have 5 "question" columns (question 1...question 2...etc) where I would like to put the formulas and one column with the combined text.

I was able to pull out the first text easily enough...but I'm struggling with the next set of text

Here is the formula I keyed in column "Question 2":

=LEFT(SUBSTITUTE([Combined Questions]@row, JOIN([Question 1@row]:[Question 1]@row, "|") + "|", ""), FIND("|", SUBSTITUTE([Combined Questions]@row, JOIN([Question 1]@row:[Question 1]@row, "|") + "|", "")) - 1)

I