Formula to split text into columns

Sabrain

I want to scan a barcode into a column in smartsheet forms with the following information:

barcode text: Name, Typ des Gerätes# QNummer$nicht zutreffend%Benutzer

As you can see there are different delimiter e.g: , # $ %

I want to split the barcode text into columns (the delimiters seperate/demonstrate the deifferent entries)

Is there any formula to split the text into columns?

My issue is that i cannot use left/right function because i want to use this function for various barcodes (always with the same order and delimiters) that dont have the same length of characters/letters

Thank you and best reagrds,

Sabrina

ker9

Hi @Sabrain

See if these will work for you. They separate the text based on the characters: , # $ %

Barcode is the column name and each formula is placed in a separate column on the same row as the barcode.

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

=MID(Barcode@row, FIND(",", Barcode@row) + 1, FIND("#", Barcode@row) - FIND(",", Barcode@row) - 1)

=MID(Barcode@row, FIND("#", Barcode@row) + 1, FIND("$", Barcode@row) - FIND("#", Barcode@row) - 1)

=MID(Barcode@row, FIND("$", Barcode@row) + 1, FIND("%", Barcode@row) - FIND("$", Barcode@row) - 1)

=MID(Barcode@row, FIND("%", Barcode@row) + 1, LEN(Barcode@row) - FIND("%", Barcode@row))

I hope this is helpful and it works for you!

ker9

Hi @Sabrain

See if these will work for you. They separate the text based on the characters: , # $ %

Barcode is the column name and each formula is placed in a separate column on the same row as the barcode.

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

=MID(Barcode@row, FIND(",", Barcode@row) + 1, FIND("#", Barcode@row) - FIND(",", Barcode@row) - 1)

=MID(Barcode@row, FIND("#", Barcode@row) + 1, FIND("$", Barcode@row) - FIND("#", Barcode@row) - 1)

=MID(Barcode@row, FIND("$", Barcode@row) + 1, FIND("%", Barcode@row) - FIND("$", Barcode@row) - 1)

=MID(Barcode@row, FIND("%", Barcode@row) + 1, LEN(Barcode@row) - FIND("%", Barcode@row))

I hope this is helpful and it works for you!

Sabrain

thank you, works perfectly

MichelleBohn

@ker9 , I am trying to do the same - \Locations\Austin Campus 2\Fifth Floor\5105 but I'm not following the formula above. I have never used the LEFT formula before. I'm looking to put the Building Name 'Austing Campus 2' in one column, Floor 'Fifth Floor' in the next column, and then room number '5105' in another. Is this possible?

Kelly Moore

Hey @MichelleBohn

The formulas will look intimidating but it's only because they have a lot of characters in them. The formula below capitalizes on the ability of the SUBSTITUTE function to designate what replacement number to act on. In your case you have multiple backslashes, which in computer language can be called CHAR(92). The formula will individually substitute the backslash that is relevant at that time with a different character (I use a "~"), so then smartsheet can use it as a placeholder. If your data is always formatted as you have in your screenshot, this formula will always work for you.

We use the MID function because you need to find and parse data that is in the middle of your textstring.

Helper Building Name

=MID([Work Location]@row, FIND("~", SUBSTITUTE([Work Location]@row, CHAR(92), "~", 2)) + 1, FIND("~", SUBSTITUTE([Work Location]@row, CHAR(92), "~", 3)) - 1 - FIND("~", SUBSTITUTE([Work Location]@row, CHAR(92), "~", 2)))

Helper Floor Name

=MID([Work Location]@row, FIND("~", SUBSTITUTE([Work Location]@row, CHAR(92), "~", 3)) + 1, FIND("~", SUBSTITUTE([Work Location]@row, CHAR(92), "~", 4)) - 1 - FIND("~", SUBSTITUTE([Work Location]@row, CHAR(92), "~", 3)))

Helper Room Number

=RIGHT([Work Location]@row, LEN([Work Location]@row) - FIND("~", SUBSTITUTE([Work Location]@row, CHAR(92), "~", 4)))

Do these work for you?

Kelly

ker9

@MichelleBohn Looks like @Kelly Moore beat me to it :) :)

I have access to the SS early adopter Generative AI so I thought I'd test this request using the following wording: Extract the text between the second and third \

Worked flawlessly but it doesn't show you the formula, which would be nice to have but I guess that is the way it's going, similar to functions in Excel that do the work for you. However, I like puzzles like these formulas.

Thank you, Kelly!

MichelleBohn

@Kelly Moore amazing!!!! It worked perfectly, and you are right without your explanation I would not have understood any of the formula, so thank you for that too. The data will always come from our system in this same way so I created your formula into a column formula so I'm ready, thanks to you.

@ker9 - Thank you also for responding. I did try the AI-generated test but I guess I wasn't clear enough for it to know what I was trying to accomplish. That is what led me to this great community for help.

Thanks again!