Removing a single character from concatenate string

Question

Hi All,This is hopefully a basic and quick fix. I've got a concatenate formula that works beautifully to join a suffix to a base number, but part of the suffix is a price, and I need to have the "." removed from the string. Here's what I have so far:IF(ISBN13!="", CONCATENATE(ISBN13, "5", {US Price}))And, for example, that would spit out:97819528473244514.99How can I get it to remove that period from the price? When I did a search for similar issues, I saw suggestions of using a substitution formula, but I wasn't sure how that would work. I'm adapting someone else's code here. Appreciate any help!

AirOps · Accepted Answer

Welcome to the Airtable Community!try this formula: SUBSTITUTE(IF(ISBN13!="", CONCATENATE(ISBN13, "5", {US PRICE})), ".", "") Some more information on the Substitute Function: SUBSTITUTE(string, old_text, new_text, [index]) This function works by first passing a string (which you created by concatenating your values together), and then as your second parameter you pass the value you would like to replace ".", in the case the period needs to be encapsulated by "" so that Airtable recognizes this value to be a string. Since you want to remove the period, you can think of it as replacing the period with nothing. You accomplish this by passing quotation marks with nothing in it "" as your third parameter. The forth parameter is optional and allows you to specify which instance of your "old_text" to replace, for example if your string had 2 periods and you put 2 in the 4th position you would only be removing the second period in your string.  Hope this helps!

mchristo33 · Answer

Welcome to the Airtable Community!try this formula: SUBSTITUTE(IF(ISBN13!="", CONCATENATE(ISBN13, "5", {US PRICE})), ".", "") Some more information on the Substitute Function: SUBSTITUTE(string, old_text, new_text, [index]) This function works by first passing a string (which you created by concatenating your values together), and then as your second parameter you pass the value you would like to replace ".", in the case the period needs to be encapsulated by "" so that Airtable recognizes this value to be a string. Since you want to remove the period, you can think of it as replacing the period with nothing. You accomplish this by passing quotation marks with nothing in it "" as your third parameter. The forth parameter is optional and allows you to specify which instance of your "old_text" to replace, for example if your string had 2 periods and you put 2 in the 4th position you would only be removing the second period in your string.  Hope this helps! Thank you so much! I was trying to add Substitution to the end of the formula, rather than wrapping it around the concatenate. It'll probably be a bit before I truly understand the syntax, but thank you for the help!

Sign up

Login with SSO

Login to the community

Login with SSO

Scanning file for viruses.

This file cannot be downloaded