[Solved] Exclude word from sentence

[dawidek990]

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? ;]

[FJCC]

This function will return the text between the last space and the end of the text.

Code: Select all

=RIGHT(A1;LEN(A1) -SEARCH("[^\s]+$";A1)+1)

You must have regular expression enabled in the menu Tools -> Options -> OpenOffice Calc -> Calculate.

[Lupp]

Split your damned compounds finally.

[dawidek990]

is working, thx ;]

[dawidek990]

Now i need to know how to leave only ABRILA BIANCO MZAIKA MIX FLOATING TILES... without 29.8x29.8?
from begining to last space ;]

[RusselB]

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

=RIGHT(A1;LEN(A1) -SEARCH("[^\s]+$";A1)+1)

is in B2, then in C2 enter

Code: Select all

=substitute(A2;B2;"")

Alternatively you could use

Code: Select all

=left(A2;len(A2)-len(B2))

[Lupp]

See attachment.

[dawidek990]

thank you very much ;]

[dawidek990]

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?

[Lupp]

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.)

[dawidek990]

cool, is working, thank you ;]

[dawidek990]

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.

[RusselB]

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.

[dawidek990]

ok, but maybe is other option to exclude thus link from <img src=''https://a.allegroimg.com/s1440/01bbc8/2 ... c35d0b0bb8'' asdhbas ahsbd ahsbd.

[RusselB]

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.

[dawidek990]

I have

<img src=''https://a.allegroimg.com/s1440/01bbc8/2 ... c35d0b0bb8''

there is 2 of quotation marks.
i need only

https://a.allegroimg.com/s1440/01bbc8/2 ... c35d0b0bb8

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/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>

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

Some like: find the first https and copy the expression to "

[dawidek990]

i modified post ;]

[RusselB]

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.

[dawidek990]

I want to delete everything, and keep only link.
I just need exclude first link to the other cell from html code ;]

[dawidek990]

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)

[RusselB]

If I'm understanding correctly,

Code: Select all

=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.

[dawidek990]

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?

[FJCC]

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

<guid>[^<]+</guid>

and use Replace All with the Replace With box empty.
Then search for

Code: Select all

</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.

[dawidek990]

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

<guid>[^<]+</guid>

and use Replace All with the Replace With box empty.
Then search for

Code: Select all

</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.

[RusselB]

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

[^<]+

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.

[dawidek990]

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.

[RusselB]

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.

[dawidek990]

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?

[RusselB]

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.

[dawidek990]

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>