[Solved] Exclude word from sentence
-
- Posts: 55
- Joined: Thu Dec 13, 2018 10:27 am
[Solved] Exclude word from sentence
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? ;]
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.
Reason: tagged solved.
OpenOffice 4.1.6 Windows 10
Re: Exclude word from sentence.
This function will return the text between the last space and the end of the text.
You must have regular expression enabled in the menu Tools -> Options -> OpenOffice Calc -> Calculate.
Code: Select all
=RIGHT(A1;LEN(A1) -SEARCH("[^\s]+$";A1)+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: Exclude word from sentence.
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 24.2 (new numbering) and older versions, PortableOpenOffice 4.1.7 and older, StarOffice 5.2
---
Lupp from München
---
Lupp from München
-
- Posts: 55
- Joined: Thu Dec 13, 2018 10:27 am
-
- Posts: 55
- Joined: Thu Dec 13, 2018 10:27 am
Re: Exclude word from sentence.
Now i need to know how to leave only ABRILA BIANCO MZAIKA MIX FLOATING TILES... without 29.8x29.8?
from begining to last space ;]
from begining to last space ;]
OpenOffice 4.1.6 Windows 10
Re: Exclude word from sentence.
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 is in B2, then in C2 enter
Alternatively you could use
So if your original text is in A2, and
Code: Select all
=RIGHT(A1;LEN(A1) -SEARCH("[^\s]+$";A1)+1)
Code: Select all
=substitute(A2;B2;"")
Code: Select all
=left(A2;len(A2)-len(B2))
OpenOffice 4.1.7, LibreOffice 7.0.1.2 on Windows 7 Pro, Ultimate & Windows 10 Home (2004)
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.
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.
Re: Exclude word from sentence.
See attachment.
- Attachments
-
- aoo97203SplitSillyCompound_1.ods
- (37.02 KiB) Downloaded 108 times
On Windows 10: LibreOffice 24.2 (new numbering) and older versions, PortableOpenOffice 4.1.7 and older, StarOffice 5.2
---
Lupp from München
---
Lupp from München
-
- Posts: 55
- Joined: Thu Dec 13, 2018 10:27 am
-
- Posts: 55
- Joined: Thu Dec 13, 2018 10:27 am
Re: Exclude word from sentence.
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?
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
Re: Exclude word from sentence.
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.)
However, see new attachment. (You may reject the macros now.)
- Attachments
-
- aoo97203SplitSillyCompound_2.ods
- (33.58 KiB) Downloaded 100 times
On Windows 10: LibreOffice 24.2 (new numbering) and older versions, PortableOpenOffice 4.1.7 and older, StarOffice 5.2
---
Lupp from München
---
Lupp from München
-
- Posts: 55
- Joined: Thu Dec 13, 2018 10:27 am
-
- Posts: 55
- Joined: Thu Dec 13, 2018 10:27 am
Re: [Solved] Exclude word from sentence
1. I have "<img src=''https://a.allegroimg.com/s1440/01bbc8/2 ... c35d0b0bb8'' 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.
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
Re: [Solved] Exclude word from sentence
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.
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, LibreOffice 7.0.1.2 on Windows 7 Pro, Ultimate & Windows 10 Home (2004)
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.
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.
-
- Posts: 55
- Joined: Thu Dec 13, 2018 10:27 am
Re: [Solved] Exclude word from sentence
ok, but maybe is other option to exclude thus link from <img src=''https://a.allegroimg.com/s1440/01bbc8/2 ... c35d0b0bb8'' asdhbas ahsbd ahsbd.
OpenOffice 4.1.6 Windows 10
Re: [Solved] Exclude word from sentence
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.
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, LibreOffice 7.0.1.2 on Windows 7 Pro, Ultimate & Windows 10 Home (2004)
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.
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.
-
- Posts: 55
- Joined: Thu Dec 13, 2018 10:27 am
Re: [Solved] Exclude word from sentence
I have
i need only
its looks like
Some like: find the first https and copy the expression to "
there is 2 of quotation marks.
i need only
but there is all html code...
its looks like
this is a middle of whole html code... and i need exclude only first https link ('https://a.allegroimg.com/s1440/01ccf7/b ... 5870ea58e4)</div>
</section>
<section class=''section''>
<div class=''item item-6''>
<section class=''image-item''>
<img src=''https://a.allegroimg.com/s1440/01ccf7/b ... 5870ea58e4'' srcset=''https://a.allegroimg.com/s360/01ccf7/b6 ... 5870ea58e4 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>
Some like: find the first https and copy the expression to "
OpenOffice 4.1.6 Windows 10
-
- Posts: 55
- Joined: Thu Dec 13, 2018 10:27 am
Re: [Solved] Exclude word from sentence
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.
If you just remove the first one, you're going to end up with an incorrectly formatted html code.
OpenOffice 4.1.7, LibreOffice 7.0.1.2 on Windows 7 Pro, Ultimate & Windows 10 Home (2004)
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.
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.
-
- Posts: 55
- Joined: Thu Dec 13, 2018 10:27 am
Re: [Solved] Exclude word from sentence
I want to delete everything, and keep only link.
I just need exclude first link to the other cell from html code ;]
I just need exclude first link to the other cell from html code ;]
OpenOffice 4.1.6 Windows 10
-
- Posts: 55
- Joined: Thu Dec 13, 2018 10:27 am
Re: [Solved] Exclude word from sentence
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)
=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
Re: [Solved] Exclude word from sentence
If I'm understanding correctly, 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.
Code: Select all
=LEFT(MID(A1;FIND("http";A1;1);LEN(A1));FIND(" ";MID(A1;FIND("http";A1;1);LEN(A1));1))
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, LibreOffice 7.0.1.2 on Windows 7 Pro, Ultimate & Windows 10 Home (2004)
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.
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.
-
- Posts: 55
- Joined: Thu Dec 13, 2018 10:27 am
Re: [Solved] Exclude word from sentence
OK this formula is ok.
But i have another problem:
I have text in one cell like:
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?
But i have another problem:
I have text in one cell like:
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 ...<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>
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
Re: [Solved] Exclude word from sentence
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
and use Replace All with the Replace With box empty.
Then search for
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.
Code: Select all
<guid>[^<]+</guid>
Then search for
Code: Select all
</id>\s*<id>
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.
-
- Posts: 55
- Joined: Thu Dec 13, 2018 10:27 am
Re: [Solved] Exclude word from sentence
when i click ctrl+f and put "<guid>[^<]+</guid>" to the find box... click repleace all... he found nothing.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 forand use Replace All with the Replace With box empty.Code: Select all
<guid>[^<]+</guid>
Then search forand 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.Code: Select all
</id>\s*<id>
OpenOffice 4.1.6 Windows 10
Re: [Solved] Exclude word from sentence
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
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.
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
[^<]+
OpenOffice 4.1.7, LibreOffice 7.0.1.2 on Windows 7 Pro, Ultimate & Windows 10 Home (2004)
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.
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.
-
- Posts: 55
- Joined: Thu Dec 13, 2018 10:27 am
Re: [Solved] Exclude word from sentence
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.
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
Re: [Solved] Exclude word from sentence
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.
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, LibreOffice 7.0.1.2 on Windows 7 Pro, Ultimate & Windows 10 Home (2004)
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.
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.
-
- Posts: 55
- Joined: Thu Dec 13, 2018 10:27 am
Re: [Solved] Exclude word from sentence
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?
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
Re: [Solved] Exclude word from sentence
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.
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, LibreOffice 7.0.1.2 on Windows 7 Pro, Ultimate & Windows 10 Home (2004)
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.
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.
-
- Posts: 55
- Joined: Thu Dec 13, 2018 10:27 am
Re: [Solved] Exclude word from sentence
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.
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