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

[eteb3]

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

[karolus]

Hallo

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

[eteb3]

@karolus

=REGEX(X16;"([A-Z]*)$")

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]

@eteb3

Makes me think it is a problem with my data?

=REGEX(TRIM( X16 );"([A-Z]*)$")   # remove whitespace before|after …

[Wanderer]

@eteb3

Makes me think it is a problem with my data?

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

[eteb3]

@Wanderer

you should upload your (reduced) file here

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

[mikekaganski]

@eteb3

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

[karolus]

@eteb3

does a pasted table not give sufficient information?

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]

@mikekaganski

if others would know to look inside the source code of that pasted data.

All I learned was not to trust discourse:

Can typographic quotes be switched off for ask.libreoffice.org?

I saw some code on this site, where typographic quotes replaced the original quotes. Example far down in this question: It contains the following block: SELECT * FROM “tArtist_Song” ORDER BY ( SELECT “Song_Title” FROM “tSongs” WHERE “ID” = “tArtist_Song”.“SongID” ) As this usually breaks the code, you can’t copy/ paste the code back. While I recommend to read anything before you paste, I don’t think it is useful on this site to have this replacement. I am assuming this smart replacement is…

[Wanderer]

@mikekaganski

if others would know to look inside the source code of that pasted data.

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>

[Wanderer]

@Wanderer

Can you explain, how you do that. Or is this something available to users with the right to edit other users posts.

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