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

Ask Your Question

How to remove spaces in calc [closed]

asked 2016-06-21 11:28:56 +0200

cje gravatar image

Hi, I have content in a cell in calc which looks like this: 830 13

I need to remove the space to make it appear lik this 83013

How may I do that?

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 2020-09-08 12:49:10.657614


Your "Karma" should be sufficient for being allowed to attach an example file.

Lupp gravatar imageLupp ( 2016-06-21 12:01:19 +0200 )edit

2 Answers

Sort by » oldest newest most voted

answered 2016-06-21 11:48:06 +0200

Lupp gravatar image

updated 2016-06-21 11:58:50 +0200

Shall it be done in situ, once and for all?
Use the tool 'Find & Replace' , enable the option 'Regular expressions',
Put * (a space followed by an asterisk) into 'Search For' and leave 'Replace With' empty.
Tick 'Current selection only' if needed and order 'Replace All'.
If the result shall not be recognized as a number (but remain text), you may have to select the option 'Text' (code @) under 'Format Cells...' > 'Numbers' > 'Category' in advance.

Shall it be done by a formula in another (adjacent?) cell, and be active there even if the original content is changed?
Suppose your text containing the unwanted spaces is in A1. Put into B1 (e.g.) the formula
=SUBSTITUTE(A1;" ";"")
Done! In this case the 'Numbers' format of the target cell must not be 'Text'. The results of SUBSTITUTE are texts anyway, and a formula would not work in a 'Text' formatted cell.

My answer is based on the assumption that the original content was text (imported from a csv e.g.).
If the "space" actually was inserted as 'Thousands Separator' by number formatting, refer to the answer by @pierre-yves samyn.
You can distinguish the cases using the tool > 'View' > 'ValueHighlighting' (Ctrl+F8) or by asking =TEXT(A1) in any blank cell.

edit flag offensive delete link more


'Find & Replace' with \s+ is more effective. (\s is the RegEx for spaces and space-like characters, and + will select multiple spaces in a row)

gerbal gravatar imagegerbal ( 2017-07-24 12:05:25 +0200 )edit

answered 2016-06-21 11:51:45 +0200

pierre-yves samyn gravatar image


I'm not sure because of your example, but this could be the thousands separator: Right clickFormat CellsNumber tab▸untick Thousand separator


edit flag offensive delete link more


@pierre-yves samyn: Out of curiosity: Do you know locales using a space as the 'Thousands separator'?

Lupp gravatar imageLupp ( 2016-06-21 12:17:46 +0200 )edit

@Lupp : french (but I think swiss typography uses an apostrophe), ISO 31-0. See also the second paragraph here.

pierre-yves samyn gravatar imagepierre-yves samyn ( 2016-06-21 14:20:34 +0200 )edit

@pierre-yves samyn: Thank you! In German nowadays often is used the dot (full stop), I am not happy with.
When I was teached a bit about accouning in high-school (in about 1959; time of accounts kept in handwriting or typewriting) we were told not to use a space to prevent forgery. (I do not remember in what way the prefixing of additional digits was prevented.
If I remeber correctly there was a more recent recommendation to use thin_space for the purpose.

Lupp gravatar imageLupp ( 2016-06-21 19:17:37 +0200 )edit

@Lupp: yup, space is the standard thousands separator in at least Finnish, Swedish, Norwegian, Danish. Possibly a tonne of other locales.

Jools gravatar imageJools ( 2018-11-22 11:31:29 +0200 )edit

I didn't know this next usage of the apostrophe. In fact I am unhappy with any locale hokum concerning data needed in wide-range communication. I consider it a bad idea for software to figure the culture-neutral just because the global community is too lazy (in thinking) to abandon that nonsense.
At least ISO expressly deprecates the point and the comma as well as group separators - and nobody cares.
Its a mess getting worse every day.
("Small space" was recommended, but is not supported.)

Lupp gravatar imageLupp ( 2018-11-22 12:34:00 +0200 )edit

Question Tools

1 follower


Asked: 2016-06-21 11:28:56 +0200

Seen: 34,086 times

Last updated: Jun 21 '16