Upcoming database upgrades. Airtable functionality will be reduced for ~15 minutes at 06:00 UTC on Feb. 4 / 10:00 pm PT on Feb. 3. Learn more here
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.
Mar 21, 2021 04:02 AM
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
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:
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:
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, ' ', '')
Mar 21, 2021 03:44 AM
@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)
Mar 21, 2021 04:02 AM
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