[Solved] Formula for split cell without using text to column

Discuss the spreadsheet application
Post Reply
Mandy22
Posts: 12
Joined: Fri Apr 16, 2021 11:06 am

[Solved] Formula for split cell without using text to column

Post by Mandy22 »

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!
Last edited by robleyd on Thu May 06, 2021 11:00 am, edited 2 times in total.
Reason: Tagged [Solved]
OpenOffice 4 on Windows 7
User avatar
robleyd
Moderator
Posts: 5055
Joined: Mon Aug 19, 2013 3:47 am
Location: Murbko, Australia

Re: Formula for split cell without using text to column

Post by robleyd »

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.1.2; SlackBuild for 24.2.1 by Eric Hameleers
Mandy22
Posts: 12
Joined: Fri Apr 16, 2021 11:06 am

Re: Formula for split cell without using text to column

Post by Mandy22 »

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?
Hi!
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
FJCC
Moderator
Posts: 9248
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: Formula for split cell without using text to column

Post by FJCC »

Here is a solution using a couple of helper columns to keep the formulas simpler.
Attachments
TextSplit.ods
(9.11 KiB) Downloaded 137 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.
Mandy22
Posts: 12
Joined: Fri Apr 16, 2021 11:06 am

Re: Formula for split cell without using text to column

Post by Mandy22 »

FJCC wrote:Here is a solution using a couple of helper columns to keep the formulas simpler.
Thanks so much for this! I'm just wondering what if there are 2 underscores?

Ex. 2_Blue_Mary Anne Thomas
OpenOffice 4 on Windows 7
FJCC
Moderator
Posts: 9248
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: Formula for split cell without using text to column

Post by FJCC »

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.
Mandy22
Posts: 12
Joined: Fri Apr 16, 2021 11:06 am

Re: Formula for split cell without using text to column

Post by Mandy22 »

FJCC wrote: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)
Yes! Got it. Thank you so much! ♥️♥️
OpenOffice 4 on Windows 7
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Formula for split cell without using text to column

Post by Villeroy »

Mandy22 wrote:
FJCC wrote: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)
Yes! Got it. Thank you so much! ♥️♥️
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.
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
Post Reply