How to auto-format an Excel cell based on another cell in another column?

Question

I'm cataloging a games items in a Google Sheet. I need to take the value from one column, format it (with underscores instead of spaces, and a .png at the end), and save it to another column.

For example, cell A268 has Bounty Hat as its text. I need cell F268 to change to bounty_hat.png

How can I accomplish this?

Answer 1

You cannot do it with just formatting cells, but you can do it with a rather simple series of steps as a single formula.

In your formula, you need to perform three steps:

1. Concatenate the string in A268 with the string constant ".png" - use the & operator
2. Substitute all occurances of " " (space) with "_" - use the SUBSTITUTE() function
3. Convert all upper case characters to lower case - use the LOWER() function

Enter the combination of the three steps as the following formula in F268:

=LOWER(SUBSTITUTE(A268&".png"," ","_"))