Removing a single character from concatenate string

[mchristo33]

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.99

How 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 solution]

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]

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!

[mchristo33]

Related to this formula, I want to craft an IF statement to add a character to the output under specific conditions, but I'm not sure how to write it since the condition is based on character count.

Essentially, if {US Price} is less than 4 digits, I need a 0 to be placed after the 5. I've found the formulas that will pull a number of characters based on positioning, but not ones that produce an output based on the number of characters in the source string.