Forum Discussion
How to split my text which I can say it is complex!
syazaaoa95 Hmm. Does your Excel have the TEXTSPLIT function? If so, you could use that to retrieve the first part of the strings like so:
=TEXTSPLIT(Table2[Data],{"IR","NP","GP"," L1"," CF"," DR"," C1"," CLC"})
You'll have to look at the results to see if any remain "unsplit" and find out which characters you can add to split that one as well. Watch out though, adding texts to the strings within the curly braces may affect ones that have currently been correctly split into ones that are now split the wrong way!
See attached.
Splitting text like this can be tricky when there are no clear delimiters! You can use a Google Sheets formula with ARRAYFORMULA, combined with REGEXEXTRACT, to separate the text into two columns.
For example:
If your text follows the same pattern (a product description followed by a list of codes), you can use something like:
=ARRAYFORMULA(REGEXEXTRACT(A1:A, "(.*?)(IR.*)")
This will split the first part (e.g., "Original Canon Case LED") into one column and the codes into another.
Feel free to share your spreadsheet if you'd like more specific help!