Substitute two consecutive spaces

[Yonathan_Cohen]

Hello, I have a problem with a SUBSTITUTE formula.
I have a text field that contains two consecutive spaces between two words.
I want to SUBSTITUTE these 2 spaces by nothing ("") but the formula doesn’t work because airtable consider these 2 spaces as infinite number of spaces.

I know it because I tried to apply on this field a substitute 3 spaces by “X” formula and the output was first_word+space+X+second_word

Something weirder: if I copy the output of my formula and paste it somewhere else, the space is not present…
But it is problematic to me because I want to compare the output of my formula with another field in airtable …

[Moonlyte]

@Yonathan_Cohen Can you write a demo dataset that illustrates this problem?

[JonathanBowen]

Hi @Yonathan_Cohen - this seems to work OK for me:

The first record has two spaces between words and the second record has one space and we can see this in the second field which shows the length of “Name”.

The replace field replaces to consecutive spaces with one space using:

SUBSTITUTE(Name, '  ', ' ')

Now both have a length of 10.

If I change the replace formula so that two consecutive spaces are replaced with no spaces/a zero length string using:

SUBSTITUTE(Name, '  ', '')

then I get this:

[Yonathan_Cohen]

@Moonlyte @JonathanBowen: Sure, look at the next pictures:
Here you can see I don’t succeed removing a space in the 2nd column.

Here is the formula that should remove spaces (I precise, a simple substitute one space doesn’t work):

When you click on the first column, you can see there is two spaces thanks to the cursor that is between the 2 spaces:

[JonathanBowen]

Hi @Yonathan_Cohen - can you explain why you have got 2 SUBSTITUTE functions in your example? If you just want to remove all spaces you can do:

SUBSTITUTE(spaces, ' ', '')

[Yonathan_Cohen]

@JonathanBowen @Moonlyte : Ok, I tried things. In fact it’s not because of the 2 spaces. Because, when I remove a space and add it then, The formula works perfectly, but when I paste a text in my first column, the formla doesn’t apply totally … Did you understand what I mean ?
Look:
First screenshot, the formula doesn’t work. There is only one space between the 2 words in the first column. I remove the space in the 1st column and reinsert this space directly (doesn’t touch the formula at all) and the formula works well… (See img 2)

[Yonathan_Cohen · Accepted solution]

Ok, I have the answer, I copied and paste the 1st column in google spreadsheet. Where the space has not been removing, there was a backspace into the cell. So it is because of a backspace…
@JonathanBowen I needed to add SUBSTITUTE(first_column, “\n”, “”)
Now it works perfectly