eteb3 | 2024-10-09 11:26:45 UTC | #1 I have tried several formulas to extract the last word in a string. In all cases the formula works **sometimes**. This I cannot understand. In column B I have `=MID(X16,FIND("☃",SUBSTITUTE(X16," ","☃",LEN(X16)-LEN(SUBSTITUTE(X16," ",""))))+1,10)` I have also tried `=TRIM(SUBSTITUTE(REGEX($X25,"[^ ]*$"),",",""))` I have checked whether the last space in A is the same sort of space: it is the same space whether B returns anything or not. Can anyone tell me what I need to change? |A|B| |---|---| |6 PRINCESS PARADE NEW ROAD DAGENHAM|DAGENHAM||23 - 27 KEMP ROAD DAGENHAM || |CHARLOTTE ROAD DAGENHAM || |CHARLOTTE ROAD DAGENHAM || |GREATFIELDS ROAD BARKING || |GROUND FLOOR PARSLOES AVENUE DAGENHAM|DAGENHAM| ------------------------- karolus | 2024-10-09 11:49:22 UTC | #2 Hallo ``` =REGEX(X16;"([A-Z]*)$") # only uppercase ascii =REGEX(X16;"(\w+)$") # any last »word« … lowercase, uppercase including underscore and »Umlauts« ``` ------------------------- eteb3 | 2024-10-09 12:00:09 UTC | #3 [quote="karolus, post:2, topic:112377"] `=REGEX(X16;"([A-Z]*)$")` [/quote] Thank you. I am getting exactly the same result. Makes me think it is a problem with my data? |STRING|`=REGEX(cell,"([A-Z]*)$")`| |---|---| |6 PRINCESS PARADE NEW ROAD DAGENHAM|DAGENHAM| |23 - 27 KEMP ROAD DAGENHAM || |CHARLOTTE ROAD DAGENHAM || |CHARLOTTE ROAD DAGENHAM || |GREATFIELDS ROAD BARKING || |GROUND FLOOR PARSLOES AVENUE DAGENHAM|DAGENHAM| ------------------------- karolus | 2024-10-09 12:07:18 UTC | #4 [quote="eteb3, post:3, topic:112377"] Makes me think it is a problem with my data? [/quote] ``` =REGEX(TRIM( X16 );"([A-Z]*)$") # remove whitespace before|after … ------------------------- Wanderer | 2024-10-09 12:12:41 UTC | #5 [quote="eteb3, post:3, topic:112377"] Makes me think it is a problem with my data? [/quote] Makes me think, you should upload your (reduced) file here, if the suggestion to use TRIM doesn't help. ------------------------- eteb3 | 2024-10-09 12:24:58 UTC | #6 [quote="Wanderer, post:5, topic:112377"] you should upload your (reduced) file here [/quote] Thanks. For this newbie, does a pasted table not give sufficient information? ------------------------- mikekaganski | 2024-10-09 12:30:37 UTC | #7 [quote="eteb3, post:6, topic:112377"] does a pasted table not give sufficient information? [/quote] Well, it in fact does *in this simple case* - *if* others would know to look inside the *source* code of that pasted data. Here it is: ```text |6 PRINCESS PARADE NEW ROAD DAGENHAM|DAGENHAM| |23 - 27 KEMP ROAD DAGENHAM || |CHARLOTTE ROAD DAGENHAM || |CHARLOTTE ROAD DAGENHAM || |GREATFIELDS ROAD BARKING || |GROUND FLOOR PARSLOES AVENUE DAGENHAM|DAGENHAM| ``` and it allows to see the difference between the first/last row, and the other rows - exactly by the space between the last character and the pipe `|` symbol that means "end of cell". ------------------------- karolus | 2024-10-09 12:31:13 UTC | #8 [quote="eteb3, post:6, topic:112377"] does a pasted table not give sufficient information? [/quote] yes it gives … after the raw-view: ``` |---|---| |6 PRINCESS PARADE NEW ROAD DAGENHAM|DAGENHAM| |23 - 27 KEMP ROAD DAGENHAM || |CHARLOTTE ROAD DAGENHAM || |CHARLOTTE ROAD DAGENHAM || |GREATFIELDS ROAD BARKING || |GROUND FLOOR PARSLOES AVENUE DAGENHAM|DAGENHAM| ``` looks like there is some whitespace after the last word ( except the first and last row ) ------------------------- Wanderer | 2024-10-09 12:33:46 UTC | #9 [quote="mikekaganski, post:7, topic:112377"] *if* others would know to look inside the *source* code of that pasted data. [/quote] All I learned was not to trust discourse: https://ask.libreoffice.org/t/can-typographic-quotes-be-switched-off-for-ask-libreoffice-org/68406 ------------------------- Wanderer | 2024-10-10 14:06:41 UTC | #10 [quote="mikekaganski, post:7, topic:112377"] *if* others would know to look inside the *source* code of that pasted data. [/quote] Can you explain, how you do that. Or is this something available to users with the right to edit other users posts. EDIT: found out myself, see below... /EDIT
For me I can only repeat: "All I learned was not to trust discourse"
If I copy the text in my favorite editor I find spaces at the end of all 5 lines before the tab, but I don't know, if this transports back here. ``` 6 PRINCESS PARADE NEW ROAD DAGENHAM DAGENHAM CHARLOTTE ROAD DAGENHAM CHARLOTTE ROAD DAGENHAM GREATFIELDS ROAD BARKING GROUND FLOOR PARSLOES AVENUE DAGENHAM DAGENHAM ``` but, if I check the source of the website all entries seem to be trimmed ``` 6 PRINCESS PARADE NEW ROAD DAGENHAM DAGENHAM CHARLOTTE ROAD DAGENHAM CHARLOTTE ROAD DAGENHAM GREATFIELDS ROAD BARKING GROUND FLOOR PARSLOES AVENUE DAGENHAM DAGENHAM ``` ------------------------- Wanderer | 2024-10-10 14:03:47 UTC | #11 [quote="Wanderer, post:10, topic:112377"] Can you explain, how you do that. Or is this something available to users with the right to edit other users posts. [/quote] OK, I found it. It is available to everybody, who knows about the feature and can find out how to construct the path. Thank you @karolus for using the term "raw view".
https://ask.libreoffice.org/raw/112377 -------------------------