Hi!
Is there any formula for splitting the cells without having to use the text to column tab? I am actually thinking of just pasting the raw file in one cell everyday and the result will show in different cells.
Ex.
2_Blue/Mary Anne Thomas
Needed Result
2 Blue Mary Anne Thomas
This is just an example. But I usually deal with 400-600 of this a day. Thank you in advance!
[Solved] Formula for split cell without using text to column
[Solved] Formula for split cell without using text to column
Last edited by robleyd on Thu May 06, 2021 11:00 am, edited 2 times in total.
Reason: Tagged [Solved]
Reason: Tagged [Solved]
OpenOffice 4 on Windows 7
Re: Formula for split cell without using text to column
Do you want the result in three adjacent cells like
|2|Blue|Mary Anne Thomas|Are the separators always underscore and forward slash, or do they vary?
Cheers
David
OS - Slackware 15 64 bit
Apache OpenOffice 4.1.15
LibreOffice 24.2.2.2; SlackBuild for 24.2.2 by Eric Hameleers
David
OS - Slackware 15 64 bit
Apache OpenOffice 4.1.15
LibreOffice 24.2.2.2; SlackBuild for 24.2.2 by Eric Hameleers
Re: Formula for split cell without using text to column
Hi!robleyd wrote:Do you want the result in three adjacent cells like|2|Blue|Mary Anne Thomas|Are the separators always underscore and forward slash, or do they vary?
Yes i want the results in three adjacent cells, if it is possible
Yes the separators are always underscore and forward slash.
OpenOffice 4 on Windows 7
Re: Formula for split cell without using text to column
Here is a solution using a couple of helper columns to keep the formulas simpler.
- Attachments
-
- TextSplit.ods
- (9.11 KiB) Downloaded 146 times
OpenOffice 4.1 on Windows 10 and Linux Mint
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
Re: Formula for split cell without using text to column
Thanks so much for this! I'm just wondering what if there are 2 underscores?FJCC wrote:Here is a solution using a couple of helper columns to keep the formulas simpler.
Ex. 2_Blue_Mary Anne Thomas
OpenOffice 4 on Windows 7
Re: Formula for split cell without using text to column
Do you mean the second separator might be an underscore or a slash? Then change th formula in C2 to
Code: Select all
=SEARCH("[_/]";A2; B2+1)
OpenOffice 4.1 on Windows 10 and Linux Mint
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
Re: Formula for split cell without using text to column
Yes! Got it. Thank you so much!FJCC wrote:Do you mean the second separator might be an underscore or a slash? Then change th formula in C2 toCode: Select all
=SEARCH("[_/]";A2; B2+1)
OpenOffice 4 on Windows 7
Re: Formula for split cell without using text to column
If this formula returns a #VALUE error in some other document, turn on Tools>Options>Calc>Calculation "Enable regular expressions in formulas". This is a per-document setting.Mandy22 wrote:Yes! Got it. Thank you so much!FJCC wrote:Do you mean the second separator might be an underscore or a slash? Then change th formula in C2 toCode: Select all
=SEARCH("[_/]";A2; B2+1)
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice