Ask Your Question

Revision history [back]

click to hide/show revision 1
initial version

Using Substitute on text in calc

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 ( : 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.