Forum Discussion
Solved
How to split my text which I can say it is complex!
Below here is my Split Text Google Spreadsheet .
You may try to see my text and how am I trying to split it. But it seems quite complex because I cannot split the sentences when there are no parentheses or any delimiter which I can help with to split it.
I want my output to be:
When spitted will be in two different columns, which First column:
1. Original Canon Case LED
2. Original Canon Key Top PCB Assembly
3. Original Canon Control Panel Volume
4. Original Canon ADF PCB Assembly
5. Original Canon Touch Screen
Second column:
1. IR2535 IR2535I IR2545 IR2545I IR ADV4045 IR ADV4051 IR ADV4245 IR ADV4251 IR ADVC2020 IR ADVC2030
2. IR ADVC5030 IR ADVC5035 IR ADVC5045
3. IR ADVC5030 IR ADVC5035 IR ADVC5045
4. IR2520 IR2525 IR2535 IR2545
5. IR ADV4025 IR ADV4035 IR ADV4051 IR ADV4225 IR ADV4235 IR ADV4245 IR ADV4251 IR ADV6065 IR ADV6075 IR ADV8205 IR ADV8285 IR ADV8295 IR ADVC5030 IR ADVC5035 IR ADVC5045
is there any way on how to help in this problem.
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!
syazaaoa95 This is very easy using Data, From range/Table.
I did this:
- Copied column A to another worksheet
- Added a heading and selected Data, From Range/Table
- Use Split column by delimiter
- Set " IR" as the delimiter (excluding the quotes)
- Selected left-most delimiter
- Clicked Close & Load drop-down and chose "Close & Load to..." and opted to load to a table.
- Thank you for the suggestion. Can I know how to get the IR back?
And also what if there some descriptions does not have IR? What should I do?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.
