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]

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 reopen merge delete

## Comments

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

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

## 2 Answers

Sort by » oldest newest most voted

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.

[Edit1]
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.
[/Edit1]

more

## Comments

'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)

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

Hi

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

Regards

more

## Comments

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

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

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

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

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

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

## Stats

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

Seen: 34,048 times

Last updated: Jun 21 '16