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

Discuss the spreadsheet application

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

Postby Mandy22 » Thu May 06, 2021 2:23 am

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

Re: Formula for split cell without using text to column

Postby robleyd » Thu May 06, 2021 4:55 am

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
Apache OpenOffice 420m2(Build:9821) - Slackware 14.2 - 64 bit
LibreOffice 6.4.6.2 (SlackBuild for 6.4.6 by Eric Hameleers) - Slackware 14.2 - 64 bit
Apache OpenOffice 4.1.4 - Windows 7 Virtual machine
User avatar
robleyd
Moderator
 
Posts: 3953
Joined: Mon Aug 19, 2013 3:47 am
Location: Murbko, Australia

Re: Formula for split cell without using text to column

Postby Mandy22 » Thu May 06, 2021 5:03 am

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

Re: Formula for split cell without using text to column

Postby FJCC » Thu May 06, 2021 5:36 am

Here is a solution using a couple of helper columns to keep the formulas simpler.
Attachments
TextSplit.ods
(9.11 KiB) Downloaded 33 times
Windows 10 and Linux Mint, since 2017
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
FJCC
Moderator
 
Posts: 8114
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: Formula for split cell without using text to column

Postby Mandy22 » Thu May 06, 2021 6:13 am

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

Re: Formula for split cell without using text to column

Postby FJCC » Thu May 06, 2021 6:28 am

Do you mean the second separator might be an underscore or a slash? Then change th formula in C2 to
Code: Select all   Expand viewCollapse view
=SEARCH("[_/]";A2; B2+1)
Windows 10 and Linux Mint, since 2017
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
FJCC
Moderator
 
Posts: 8114
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: Formula for split cell without using text to column

Postby Mandy22 » Thu May 06, 2021 9:56 am

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   Expand viewCollapse view
=SEARCH("[_/]";A2; B2+1)


Yes! Got it. Thank you so much! ♥️♥️
OpenOffice 4 on Windows 7
Mandy22
 
Posts: 9
Joined: Fri Apr 16, 2021 11:06 am

Re: Formula for split cell without using text to column

Postby Villeroy » Thu May 06, 2021 10:09 am

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   Expand viewCollapse view
=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
User avatar
Villeroy
Volunteer
 
Posts: 29713
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany


Return to Calc

Who is online

Users browsing this forum: drlock and 6 guests