How do I concatenate a string literal of my cell to a string of another cell?

Question

I am trying to make life easier for myself by organising my teaching materials for private tutees of mine from various subjects/educational levels in google spreadsheets.

I want to know how I can take a string literal such as "Paper " and concatenate it to the string of characters as they appear in another cell using a formula.

Answer 1

It looks like you want the & operator

I'm not quite clear which thing you are concatenating to which in the question; however, you can do either

=$BJ$2 & "24"

or

=$BJ$2 & BH1

or

="Paper" & BH1

assuming you are on row 2 and the number is on row 1 (the $ locks the cell so that it doesn't change when copying/pasting).

Here's an example with a test spreadsheet:

Answer 2

It seems like there's an alternative option.

For a similar case, I used Array Formula and SUBSTITUTE hack. It makes formulas readable and helps with more complex joins. I found this option in this article. It worked smoothly.

I hope it'll help you too