Why does formula for last word work sometimes, not others?

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
CHARLOTTE ROAD DAGENHAM
CHARLOTTE ROAD DAGENHAM
GREATFIELDS ROAD BARKING
GROUND FLOOR PARSLOES AVENUE DAGENHAM DAGENHAM

Hallo

=REGEX(X16;"([A-Z]*)$")  # only uppercase ascii 
=REGEX(X16;"(\w+)$")     # any last »word« … lowercase, uppercase including underscore and »Umlauts«
2 Likes

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
=REGEX(TRIM( X16 );"([A-Z]*)$")   # remove whitespace before|after …
1 Like

Makes me think, you should upload your (reduced) file here, if the suggestion to use TRIM doesn’t help.

2 Likes

Thanks. For this newbie, does a pasted table not give sufficient information?

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:

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

1 Like

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 )

All I learned was not to trust discourse:

1 Like

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

<tr>
<td>6 PRINCESS PARADE NEW ROAD DAGENHAM</td>
<td>DAGENHAM</td>
</tr>
<tr>
<td>CHARLOTTE ROAD DAGENHAM</td>
<td></td>
</tr>
<tr>
<td>CHARLOTTE ROAD DAGENHAM</td>
<td></td>
</tr>
<tr>
<td>GREATFIELDS ROAD BARKING</td>
<td></td>
</tr>
<tr>
<td>GROUND FLOOR PARSLOES AVENUE DAGENHAM</td>
<td>DAGENHAM</td>

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