Help

Substitute two consecutive spaces

Topic Labels: Formulas
Solved
Jump to Solution
2628 6
cancel
Showing results for 
Search instead for 
Did you mean: 
Yonathan_Cohen
5 - Automation Enthusiast
5 - Automation Enthusiast

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 …

1 Solution

Accepted Solutions
Yonathan_Cohen
5 - Automation Enthusiast
5 - Automation Enthusiast

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

See Solution in Thread

6 Replies 6
Moonlyte
5 - Automation Enthusiast
5 - Automation Enthusiast

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

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

Screenshot 2021-03-15 at 20.41.01

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:

Screenshot 2021-03-15 at 20.44.48

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

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

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

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

Screenshot 2021-03-21 at 09.50.41

Yonathan_Cohen
5 - Automation Enthusiast
5 - Automation Enthusiast

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

image

image

Yonathan_Cohen
5 - Automation Enthusiast
5 - Automation Enthusiast

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