We will be migrating from Ask to Discourse on the first week of August, read the details here

Ask Your Question

Using Substitute on text in calc [closed]

asked 2018-03-09 17:32:35 +0200

OldJim gravatar image

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.

edit retag flag offensive reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by Alex Kemp
close date 2021-04-25 20:23:30.067410

2 Answers

Sort by » oldest newest most voted

answered 2018-03-11 07:29:16 +0200

Jim K gravatar image

updated 2018-03-11 17:52:43 +0200

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, ( ).

edit flag offensive delete link more

answered 2018-03-09 22:35:09 +0200

m.a.riosv gravatar image

It works for me.

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

Attached sample file

edit flag offensive delete link more


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.

Jim K gravatar imageJim K ( 2018-03-11 07:27:23 +0200 )edit

Question Tools

1 follower


Asked: 2018-03-09 17:32:35 +0200

Seen: 1,547 times

Last updated: Mar 11 '18