Google Sheets - Split text by multiple delimiters

Hi,

I am trying to split text by multiple delimiters ; and CHAR(10) in Google Sheets with the below formula.

=SPLIT(F2,{";",CHAR(10)},1,1)

It only is splitting for the first delimiter listed in the formula, so in the above formula it is only splitting by the ";". If I move the CHAR(10) to the be the first one, it only splits by that.

What I am doing wrong here?

Thanks

Originally Posted by HansDouwe

Please try

Formula:

Please Login or Register  to view this content.

That works great, thank you!

Would it be possible to add a character to each piece of split text within the same formula? I'd like to add an apostrophe to each bit of split text "'''.

Thanks

Originally Posted by HansDouwe

Thanks for your feedback and rep .

This formula add an apostrophe to each bit of split text:

Formula:

Please Login or Register  to view this content.

It only seems to work for the first bit of text before the first delimiter and then doesn't split the rest of the text.

It also adds it so it is visible. I am using the apostrophe so Google Sheets doesn't try and format certain dates and convert them to random text. When I add the apostrophe before a date, it prevents this.

I guess that isn't possible, thanks anyway!

I guess you need to add "ARRAYFORMULA" to the formula given by HansDouwe ;

Please Login or Register  to view this content.

Originally Posted by Haluk

I guess you need to add "ARRAYFORMULA" to the formula given by HansDouwe ;

Please Login or Register  to view this content.

Thanks, that works but is still showing the apostrophe rather than it being a hidden apostrophe like when I add it manually in front of text.

Thanks, but it produces the same result.

Google Sheets must make it so it has to be done manually to be a hidden apostrophe?

I guess, you need a NULL character..... so, this will help you;

Please Login or Register  to view this content.

Originally Posted by Haluk

I guess, you need a NULL character..... so, this will help you;

Please Login or Register  to view this content.

Unfortunately I think it is only an apostrophe that works to stop Google Sheets converting dates into random text. But it appears it must be added manually to the original text :/ to make it an invisible apostrophe :/

The last alternative works as desired on my end.

If it's not the case for you, give a link to your sheet and let others to see what's going on...

Sure, please see https://docs.google.com/spreadsheets...it?usp=sharing

If you see where the date gets split into column J, it gets converted.

So "02/01/2023" is becoming "44958".

May be this;

Please Login or Register  to view this content.

Originally Posted by Haluk

May be this;

Please Login or Register  to view this content.

This kind of works, but only when the date is entered with a / and it does just change the format of the date to use a - . I need to keep it as it was originally entered.

This is user input data so sometimes users enter a dates in various formats unfortunately there is nothing I can do about this :/

Originally Posted by MrShorty

Why is this a problem? 44958 is the serial number that corresponds to 1 Feb 2023 (and you should be able to see that if you format the cell with some kind of date format). The only problem I see with this output is if the date was intended to be 2 Jan 2023, in which case you need to check your google sheets settings to see what date convention (MDY or DMY or other) that it is using when it interprets text strings as dates.

Edit to add: If there is a question about locale (these should be read as DMY dates, where the current version is interpreting as MDY dates), this essay has a good discussion of how Google Sheets (and other spreadsheets) store dates and times, along with a discussion of how to change locale settings in Sheets: https://www.ablebits.com/office-addi...e-date-format/

It is a problem because I need the date to be exactly as the user entered it. I also work with international dates, so it just needs to be treated as plain text without being converting to the serial number.

Originally Posted by MrShorty

Another note. I don't know if my Google sheets is different (I doubt it), but I interpret the "delimiter" argument differently. Rather than a parametric array, I see it as a text string. I used =SPLIT(A3,";"&CHAR(10),1,1), and that seems to work just fine without the more complicated SUBSTITUTES() that you are currently using. Note that the "delimiter" argument is a single argument containing all of the desired characters (in this case, it contains a semicolon and a carriage return character).

I don't know if that helps or confuses. I'm hoping it helps.

Thanks, that does also work. I guess I using a comma instead of an & in my original formula which was why it wasn't working!

The text/date and other information is printed onto custom products, so it needs to be displayed exactly how it was entered, as that is how they have requested it to be. For this reason, it can't be altered at all.

I can't make sure that G Sheets can read it, as like I said, I have no control over how the user enters the date. It can be many different formats. Some G Sheets understands, some it doesn't. Also, like I have said, it needs to be exactly how it was entered, not converted to a different format.

The only way I know to prevent it is to add an apostrophe before the original date text which is what we were trying to accomplish earlier on in the thread.

Originally Posted by rorya

Try including the quote in the substitution and prefixing the cell with a quote in case the date is at the start:

=ARRAYFORMULA(SPLIT(substitute(substitute("'"&F2,CHAR(10),";"),";",";'"),";",1,1)&"")

Amazing, that worked, thanks so much!

Originally Posted by rorya

Try including the quote in the substitution and prefixing the cell with a quote in case the date is at the start:

=ARRAYFORMULA(SPLIT(substitute(substitute("'"&F2,CHAR(10),";"),";",";'"),";",1,1)&"")

Is it possible to add the TRIM function to this formula? So that any spaces are removed in the text that is split?

Originally Posted by rorya

Yes - this should work:

=ARRAYFORMULA(TRIM(SPLIT(substitute(substitute("'"&F2,CHAR(10),";"),";",";'"),";",1,1)&""))

Amazing, thanks!

I wish they had a buy beer/coffee button on here!

How to set multiple delimiters? like? ";," ?