Using Substitute on text in calc

[OldJim]

I am trying to replace a piece of text within a cell while keeping a part of the original text.

Here is the original text that would be in one cell:
Find A Grave, database and images (https://www.findagrave.com : accessed 9 March 2018), memorial page for John Crawford Lynch, Jr (2 Nov 1898–10 Feb 1958), Find A Grave Memorial no. 37178905, citing Saint Thomas Episcopal Church Cemetery, Glassboro, Gloucester County, New Jersey, USA ; Maintained by bobo (contributor 47304269) .

I want to replace the portion in bold with “{{FindAGrave|37178905}}”

I tried using =SUBSTITUTE(C5,“Find A Grave Memorial no.”,"{{FindAGrave|") which works on the first part as long as I don’t use the trailing space after no. If I use the trailing space within the quotes =SUBSTITUTE(C5,“Find A Grave Memorial no. “,”{{FindAGrave|”) the text doesn’t change.

That still doesn’t address the closing “}}”.

I know I can do this with find and replace but ideally I would be able to just paste the original text in one cell and copy the text in the other cell for use where I need it.

[jimk]

The problem is that the space is 0xA0 NO-BREAK SPACE, not an ordinary space. The underlying code point can be seen in Calc by clicking in the input line after the space and then pressing Alt+x. Requires a recent version, maybe LO 5.1 or higher.

To fix this problem, copy the non-breaking space from the original text in Calc and then paste it into the SUBSTITUTE expression.

Attached sample file.ods

Alternatively, edit the original text and change the non-breaking space to an ordinary space. You might want to use Find & Replace to fix all occurrences.

Note: I could not find a way to display a literal non-breaking space on this wiki. It gets converted to 0x20, an ordinary space. For example, ( ).

[mariosv]

It works for me.

=SUBSTITUTE(A5;"Find A Grave Memorial no. ";"{{FindAGrave|")

Attached sample file

[jimk]

Sorry for the downvote. It’s a good guess, but I thought this answer should be at 0 because it does not address the actual problem. To see what’s really going on, you have to get the original data by clicking “edit” on the question.