This website uses Cookies. Click Accept to agree to our website's cookie use as described in our Privacy Policy. Click Preferences to customize your cookie settings.

Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Airtable Community
- Discussions
- Ask A Question
- Formulas
- Substitute two consecutive spaces

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page

Topic Labels:
Formulas

Solved

Jump to Solution

0
1696
6

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Comment Post Options

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Mar 15, 2021 03:01 AM

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 …

Solved! Go to Solution.

1 Solution

Accepted Solutions

Solved
See Solution in Thread

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Mar 21, 2021 04:02 AM

@JonathanBowen I needed to add SUBSTITUTE(first_column, “\n”, “”)

Now it works perfectly

Reply

6 Replies 6

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Mar 15, 2021 01:17 PM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Mar 15, 2021 01:45 PM

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:

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Mar 16, 2021 12:16 AM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Mar 21, 2021 02:50 AM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Mar 21, 2021 03:44 AM

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)

Solved
See Solution in Thread

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Mar 21, 2021 04:02 AM

@JonathanBowen I needed to add SUBSTITUTE(first_column, “\n”, “”)

Now it works perfectly

Reply