[Solved] Exclude word from sentence

Discuss the spreadsheet application

[Solved] Exclude word from sentence

Postby dawidek990 » Fri Mar 01, 2019 8:12 pm

Hello, does someone know if there is any function or combination of functions that could exclude from the sentence only what is from the end to the last space?
for example, ABRILA BIANCO MZAIKA MIX FLOATING TILES 29.8x29.8
I need only 29.8X29.8

I know that eg function =LEN(F2)-LEN(SUBSTITUTE(F2;"X";"")) shows me how much X is in a given cell.

can anyone help? ;]
Last edited by Hagar Delest on Sun Mar 03, 2019 12:36 pm, edited 1 time in total.
Reason: tagged solved.
OpenOffice 4.1.6 Windows 10
dawidek990
 
Posts: 52
Joined: Thu Dec 13, 2018 10:27 am

Re: Exclude word from sentence.

Postby FJCC » Fri Mar 01, 2019 9:45 pm

This function will return the text between the last space and the end of the text.
Code: Select all   Expand viewCollapse view
=RIGHT(A1;LEN(A1) -SEARCH("[^\s]+$";A1)+1)

You must have regular expression enabled in the menu Tools -> Options -> OpenOffice Calc -> Calculate.
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: 7396
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: Exclude word from sentence.

Postby Lupp » Fri Mar 01, 2019 9:53 pm

Split your damned compounds finally.
Last edited by Lupp on Fri Mar 01, 2019 10:01 pm, edited 1 time in total.
On Windows 10: LibreOffice 6.2 and older versions, PortableOpenOffice 4.1.5 and older, StarOffice 5.2
---
Lupp from München
User avatar
Lupp
Volunteer
 
Posts: 2571
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

Re: Exclude word from sentence.

Postby dawidek990 » Fri Mar 01, 2019 9:53 pm

is working, thx ;]
OpenOffice 4.1.6 Windows 10
dawidek990
 
Posts: 52
Joined: Thu Dec 13, 2018 10:27 am

Re: Exclude word from sentence.

Postby dawidek990 » Sat Mar 02, 2019 11:37 am

Now i need to know how to leave only ABRILA BIANCO MZAIKA MIX FLOATING TILES... without 29.8x29.8?
from begining to last space ;]
OpenOffice 4.1.6 Windows 10
dawidek990
 
Posts: 52
Joined: Thu Dec 13, 2018 10:27 am

Re: Exclude word from sentence.

Postby RusselB » Sat Mar 02, 2019 6:36 pm

Since you have the code to get the part that you now want to remove, simply use the SUBSTITUTE function.
So if your original text is in A2, and
Code: Select all   Expand viewCollapse view
=RIGHT(A1;LEN(A1) -SEARCH("[^\s]+$";A1)+1)
is in B2, then in C2 enter
Code: Select all   Expand viewCollapse view
=substitute(A2;B2;"")

Alternatively you could use
Code: Select all   Expand viewCollapse view
=left(A2;len(A2)-len(B2))
OpenOffice 4.1.7 and LibreOffice 6.3.3.2 on Windows 7 Pro & Ultimate
If you believe your problem has been resolved, please go to your first post in this topic, click the Edit button and add [Solved] to the beginning of the Subject line.
User avatar
RusselB
Moderator
 
Posts: 5703
Joined: Fri Jan 03, 2014 7:31 am
Location: Sarnia, ON

Re: Exclude word from sentence.

Postby Lupp » Sat Mar 02, 2019 8:18 pm

See attachment.
Attachments
aoo97203SplitSillyCompound_1.ods
(37.02 KiB) Downloaded 17 times
On Windows 10: LibreOffice 6.2 and older versions, PortableOpenOffice 4.1.5 and older, StarOffice 5.2
---
Lupp from München
User avatar
Lupp
Volunteer
 
Posts: 2571
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

Re: Exclude word from sentence.

Postby dawidek990 » Sat Mar 02, 2019 9:50 pm

thank you very much ;]
OpenOffice 4.1.6 Windows 10
dawidek990
 
Posts: 52
Joined: Thu Dec 13, 2018 10:27 am

Re: Exclude word from sentence.

Postby dawidek990 » Sat Mar 02, 2019 10:11 pm

I have an additional question, if the name was: TILES PARADYŻ ABRILA 29.8X29.8 BIANCO MOSAIC MIX
and I would like to exclude 29,8X29,8, so let's assume that the formula is looking for "X" and is cutting to the space before and after it?
OpenOffice 4.1.6 Windows 10
dawidek990
 
Posts: 52
Joined: Thu Dec 13, 2018 10:27 am

Re: Exclude word from sentence.

Postby Lupp » Sat Mar 02, 2019 10:37 pm

That's still the wrong approach. Much too complicated. Basically split your compounds in advance completely.

However, see new attachment. (You may reject the macros now.)
Attachments
aoo97203SplitSillyCompound_2.ods
(33.58 KiB) Downloaded 22 times
On Windows 10: LibreOffice 6.2 and older versions, PortableOpenOffice 4.1.5 and older, StarOffice 5.2
---
Lupp from München
User avatar
Lupp
Volunteer
 
Posts: 2571
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

Re: Exclude word from sentence.

Postby dawidek990 » Sat Mar 02, 2019 10:54 pm

cool, is working, thank you ;]
OpenOffice 4.1.6 Windows 10
dawidek990
 
Posts: 52
Joined: Thu Dec 13, 2018 10:27 am

Re: [Solved] Exclude word from sentence

Postby dawidek990 » Mon Jul 08, 2019 6:32 pm

1. I have "<img src=''https://a.allegroimg.com/s1440/01bbc8/277586794e3baa4f63c35d0b0bb8'' asdhbas ahsbd ahsbd"
i need exclude only link: https://a.allegroimg.com/s1440/01bbc8/2 ... c35d0b0bb8
how can I do this?

2. Tell me please one thing...I modified it so that it would recognize letters too... (?<=(^| ))[a-z;0-9]+(\.[a-z;0-9]+)?X[a-z;0-9]+(\.[a-z;0-9]+)?(?=( |$))
but how to add all symbols? .,/;'[]!@#$% etc? or just everything?
I see this thing "\." and i cant change it to any symbole... but only one.
OpenOffice 4.1.6 Windows 10
dawidek990
 
Posts: 52
Joined: Thu Dec 13, 2018 10:27 am

Re: [Solved] Exclude word from sentence

Postby RusselB » Mon Jul 08, 2019 6:46 pm

Some of those characters are special for regex, so if you want to get the specific character, you have to precede it with a \
There is a list of regular expressions in the Calc help file. Use the Index tab and search for regular expressions;list of The italics is just to indicate the actual search term.
While it's not a full list of all of the possibilities, reading through that and combining the information from different parts of that reading should enable you to come up with a regex term that works. With the way it looks now, adding the extra may cause your final code to be significantly longer... unless there are ways of combining terms other than what I know of.
OpenOffice 4.1.7 and LibreOffice 6.3.3.2 on Windows 7 Pro & Ultimate
If you believe your problem has been resolved, please go to your first post in this topic, click the Edit button and add [Solved] to the beginning of the Subject line.
User avatar
RusselB
Moderator
 
Posts: 5703
Joined: Fri Jan 03, 2014 7:31 am
Location: Sarnia, ON

Re: [Solved] Exclude word from sentence

Postby dawidek990 » Mon Jul 08, 2019 6:50 pm

ok, but maybe is other option to exclude thus link from <img src=''https://a.allegroimg.com/s1440/01bbc8/277586794e3baa4f63c35d0b0bb8'' asdhbas ahsbd ahsbd.
OpenOffice 4.1.6 Windows 10
dawidek990
 
Posts: 52
Joined: Thu Dec 13, 2018 10:27 am

Re: [Solved] Exclude word from sentence

Postby RusselB » Mon Jul 08, 2019 6:51 pm

Relooking at your first question in your last post, I have to ask, is that term you posted, including all of the quotation marks the entire thing, or did you manually add the quotation marks at the beginning and end?
Either option is doable, but what I'm thinking of is easier if there's only one set of quotation marks to handle... ie: the quotes that are around the section you actually want to remove.
OpenOffice 4.1.7 and LibreOffice 6.3.3.2 on Windows 7 Pro & Ultimate
If you believe your problem has been resolved, please go to your first post in this topic, click the Edit button and add [Solved] to the beginning of the Subject line.
User avatar
RusselB
Moderator
 
Posts: 5703
Joined: Fri Jan 03, 2014 7:31 am
Location: Sarnia, ON

Re: [Solved] Exclude word from sentence

Postby dawidek990 » Mon Jul 08, 2019 6:55 pm

I have
<img src=''https://a.allegroimg.com/s1440/01bbc8/277586794e3baa4f63c35d0b0bb8''

there is 2 of quotation marks.
i need only

https://a.allegroimg.com/s1440/01bbc8/277586794e3baa4f63c35d0b0bb8


but there is all html code...
its looks like
</div>

</section>

<section class=''section''>

<div class=''item item-6''>
<section class=''image-item''>
<img src=''https://a.allegroimg.com/s1440/01ccf7/b6dc32034eb491aec55870ea58e4'' srcset=''https://a.allegroimg.com/s360/01ccf7/b6dc32034eb491aec55870ea58e4 360w, https://a.allegroimg.com/s720/01ccf7/b6 ... 5870ea58e4 720w, https://a.allegroimg.com/s1024/01ccf7/b ... 5870ea58e4 806w'' sizes=''(min-width: 1200px) 560px, (min-width: 992px) 50vw, 100vw'' width=''806px''/>
</section>

</div>


this is a middle of whole html code... and i need exclude only first https link ('https://a.allegroimg.com/s1440/01ccf7/b6dc32034eb491aec55870ea58e4)

Some like: find the first https and copy the expression to "
OpenOffice 4.1.6 Windows 10
dawidek990
 
Posts: 52
Joined: Thu Dec 13, 2018 10:27 am

Re: [Solved] Exclude word from sentence

Postby dawidek990 » Mon Jul 08, 2019 7:07 pm

i modified post ;]
OpenOffice 4.1.6 Windows 10
dawidek990
 
Posts: 52
Joined: Thu Dec 13, 2018 10:27 am

Re: [Solved] Exclude word from sentence

Postby RusselB » Mon Jul 08, 2019 7:10 pm

You have that https string a few times. Is that the part you want to keep, or is that the part you want to remove? If it's to be removed, is it removed from all spots in the html code or just the first one?
If you just remove the first one, you're going to end up with an incorrectly formatted html code.
OpenOffice 4.1.7 and LibreOffice 6.3.3.2 on Windows 7 Pro & Ultimate
If you believe your problem has been resolved, please go to your first post in this topic, click the Edit button and add [Solved] to the beginning of the Subject line.
User avatar
RusselB
Moderator
 
Posts: 5703
Joined: Fri Jan 03, 2014 7:31 am
Location: Sarnia, ON

Re: [Solved] Exclude word from sentence

Postby dawidek990 » Mon Jul 08, 2019 7:12 pm

I want to delete everything, and keep only link.
I just need exclude first link to the other cell from html code ;]
OpenOffice 4.1.6 Windows 10
dawidek990
 
Posts: 52
Joined: Thu Dec 13, 2018 10:27 am

Re: [Solved] Exclude word from sentence

Postby dawidek990 » Mon Jul 08, 2019 8:02 pm

ok, I make one... but wokring only with first link, and is long:
=right(left(A1;find("https";A1));len(left(A1;find("https";A1)))-search("[^\s]+$";left(A1;find("https";A1)))-5)&left(right(A1;len(A1)-find("https";A1));find(" ";right(A1;len(A1)-find("https";A1)))-3)
OpenOffice 4.1.6 Windows 10
dawidek990
 
Posts: 52
Joined: Thu Dec 13, 2018 10:27 am

Re: [Solved] Exclude word from sentence

Postby RusselB » Tue Jul 09, 2019 12:16 am

If I'm understanding correctly,
Code: Select all   Expand viewCollapse view
=LEFT(MID(A1;FIND("http";A1;1);LEN(A1));FIND(" ";MID(A1;FIND("http";A1;1);LEN(A1));1))
brings back what you are asking for, based on your example.
I do note that this code includes the '' at the end of the URL. Normally I would just account for that by subtracting a character from the overall length, however, in the case of your example, that has been entered as 2 single quotes, rather than 1 double quote.
Not knowing if this was done intentionally or not I opted to leave the characters in place.
If you don't want them, then the code is easily amendable to remove them, but I would need to know if they need to be removed as a double quote or as 2 single quotes.
OpenOffice 4.1.7 and LibreOffice 6.3.3.2 on Windows 7 Pro & Ultimate
If you believe your problem has been resolved, please go to your first post in this topic, click the Edit button and add [Solved] to the beginning of the Subject line.
User avatar
RusselB
Moderator
 
Posts: 5703
Joined: Fri Jan 03, 2014 7:31 am
Location: Sarnia, ON

Re: [Solved] Exclude word from sentence

Postby dawidek990 » Sun Jul 14, 2019 6:03 pm

OK this formula is ok.

But i have another problem:
I have text in one cell like:
<id>550124</id><guid>238974shdbf23874dgf23hr</guid><id>550125</id><guid>238974shdbf23874dgf23hr</guid><id>550126</id><guid>238974shdbf23874dgf23hr</guid><id>5503454</id> <guid>238974shdbf23874dgf23hr</guid><id>550456</id> <guid>238974shdbf23874dgf23hr</guid><id>550456</id><guid>238974shdbf23874dgf23hr</guid>


I need the formula to separate all id numbers, it can be separated into separate cells, it can be in one cell, but not one, all the id numbers that are in the cell ...
Is this possible?

that is, I should get: 550124 550125 550126 5503454 550456 550456 ... these id is hundreds usually and the division of text into columns in a calc is limited.
every id starts with 550 ... maybe it will help?
OpenOffice 4.1.6 Windows 10
dawidek990
 
Posts: 52
Joined: Thu Dec 13, 2018 10:27 am

Re: [Solved] Exclude word from sentence

Postby FJCC » Sun Jul 14, 2019 6:54 pm

If you are willing to use Find & Replace in a couple of steps, you can do the following. This assumes that, like your example, there are only two types of tags. First search for
Code: Select all   Expand viewCollapse view
<guid>[^<]+</guid>

and use Replace All with the Replace With box empty.
Then search for
Code: Select all   Expand viewCollapse view
</id>\s*<id>

and use Replace All with the Replace With box containing a comma. That will leave you with just the lead and trailing id tags to delete. You can then use Text To Columns to split the numbers into individual cells.
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: 7396
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: [Solved] Exclude word from sentence

Postby dawidek990 » Sun Jul 14, 2019 7:21 pm

FJCC wrote:If you are willing to use Find & Replace in a couple of steps, you can do the following. This assumes that, like your example, there are only two types of tags. First search for
Code: Select all   Expand viewCollapse view
<guid>[^<]+</guid>

and use Replace All with the Replace With box empty.
Then search for
Code: Select all   Expand viewCollapse view
</id>\s*<id>

and use Replace All with the Replace With box containing a comma. That will leave you with just the lead and trailing id tags to delete. You can then use Text To Columns to split the numbers into individual cells.

when i click ctrl+f and put "<guid>[^<]+</guid>" to the find box... click repleace all... he found nothing.
OpenOffice 4.1.6 Windows 10
dawidek990
 
Posts: 52
Joined: Thu Dec 13, 2018 10:27 am

Re: [Solved] Exclude word from sentence

Postby RusselB » Sun Jul 14, 2019 8:09 pm

Check the Find & Replace dialog and ensure that the Entire Cells option is unchecked.
If that is unchecked, then go to your source and do a copy & paste of a sample of your matching source into the Find box.
Do NOT do the search, but go into the Find box and edit the section between the <guid> and </guid> from your paste replacing it with the
Code: Select all   Expand viewCollapse view
[^<]+

Then perform the search & replace. If that still doesn't work, please provide us with a link to your source, otherwise we will continue shooting in the dark.
OpenOffice 4.1.7 and LibreOffice 6.3.3.2 on Windows 7 Pro & Ultimate
If you believe your problem has been resolved, please go to your first post in this topic, click the Edit button and add [Solved] to the beginning of the Subject line.
User avatar
RusselB
Moderator
 
Posts: 5703
Joined: Fri Jan 03, 2014 7:31 am
Location: Sarnia, ON

Re: [Solved] Exclude word from sentence

Postby dawidek990 » Sun Jul 14, 2019 8:20 pm

i have polish version of openoffice, but this is not problem i think.

when i paste "<guid>[^<]+</guid>" to the cell, and click find and replace... he finds and replaces.
but not replace any cells with id numbers, because he looking for exactly "<guid>[^<]+</guid>" or "<id>[^<]+</id>", not numbers what is between.

Entire cells is not chcecked.
OpenOffice 4.1.6 Windows 10
dawidek990
 
Posts: 52
Joined: Thu Dec 13, 2018 10:27 am

Re: [Solved] Exclude word from sentence

Postby RusselB » Sun Jul 14, 2019 8:24 pm

Sounds like the first step from FJCC's post was followed successfully. Did you do the second, third and fourth steps?
Second step is to do the Find & Replace with the second code provided by FJCC.
Third step is to remove the ID tags.
Fourth step is to use the Text to Columns.
OpenOffice 4.1.7 and LibreOffice 6.3.3.2 on Windows 7 Pro & Ultimate
If you believe your problem has been resolved, please go to your first post in this topic, click the Edit button and add [Solved] to the beginning of the Subject line.
User avatar
RusselB
Moderator
 
Posts: 5703
Joined: Fri Jan 03, 2014 7:31 am
Location: Sarnia, ON

Re: [Solved] Exclude word from sentence

Postby dawidek990 » Sun Jul 14, 2019 8:33 pm

I have one cell "<id>550125</id>
I understand that entering the box (find and replace) "</id>\s*<id>" should remove <id> and </id> and leave only the number ... it is right?
OpenOffice 4.1.6 Windows 10
dawidek990
 
Posts: 52
Joined: Thu Dec 13, 2018 10:27 am

Re: [Solved] Exclude word from sentence

Postby RusselB » Sun Jul 14, 2019 9:11 pm

No.
From FJCC's post: That will leave you with just the lead and trailing id tags to delete. You can then use Text To Columns to split the numbers into individual cells.
OpenOffice 4.1.7 and LibreOffice 6.3.3.2 on Windows 7 Pro & Ultimate
If you believe your problem has been resolved, please go to your first post in this topic, click the Edit button and add [Solved] to the beginning of the Subject line.
User avatar
RusselB
Moderator
 
Posts: 5703
Joined: Fri Jan 03, 2014 7:31 am
Location: Sarnia, ON

Re: [Solved] Exclude word from sentence

Postby dawidek990 » Mon Jul 15, 2019 12:45 pm

ok, but is not working to me.

ok, so other thing. A need formula to exclude one first number id, and another formula to exclude second number id in cell... this is possible?
and i can modifing this formula to third, fourth fifth etc.
<id>550124</id><guid>238974shdbf23874dgf23hr</guid><id>550125</id><guid>238974shdbf23874dgf23hr</guid><id>550126</id><guid>238974shdbf23874dgf23hr</guid><id>5503454</id> <guid>238974shdbf23874dgf23hr</guid><id>550456</id> <guid>238974shdbf23874dgf23hr</guid><id>550456</id><guid>238974shdbf23874dgf23hr</guid>
OpenOffice 4.1.6 Windows 10
dawidek990
 
Posts: 52
Joined: Thu Dec 13, 2018 10:27 am


Return to Calc

Who is online

Users browsing this forum: Google [Bot] and 10 guests