Google Sheets is one of popular spreadsheet applications for personal purpose. It’s easy to use and share contents while it’s versatile to meet complex needs.

Recently someone asked me a question about it — how can she reverse a string and flip case of letters. For example, if a cell has a value “AbC123”, she wanted it to be “321cBa” with a formula. She has googled of course but could not find an immediate answer.

I did some research and came up with the following formula

=ARRAYFORMULA(concatenate(IF(exact(MID(A1,LEN(A1)-sequence(LEN(A1))+1,1),upper(MID(A1,LEN(A1)-sequence(LEN(A1))+1,1))), lower(MID(A1,LEN(A1)-sequence(LEN(A1))+1,1)), upper(MID(A1,LEN(A1)-sequence(LEN(A1))+1,1)))))

It’s long and complecated. But when if we look it into smaller units — functions, it’s easy to understand.

1. First it uses the sequence and len functions to create an arrary of numbers. For example, if the string length is 8, it will create an arrary of {1:8}. You may see old ways to generate a vector using the functions indirect and row, for example “=ROW(INDIRECT("1:"&LEN(A1)))“, but sequence function provides a simpler way.

2. Then use the functions mid and len to get each character of the string with the order reversed.

=MID(A1,LEN(A1)-sequence(LEN(A1))+1,1)

3. Use the function exact to see if the character is a upper case or not. If it’s non-letter, nothing happens.

=exact(MID(A1,LEN(A1)-sequence(LEN(A1))+1,1),upper(MID(A1,LEN(A1)-sequence(LEN(A1))+1,1)))

4. Combine with the function if, flip case:

=IF(exact(MID(A1,LEN(A1)-sequence(LEN(A1))+1,1),upper(MID(A1,LEN(A1)-sequence(LEN(A1))+1,1))), lower(MID(A1,LEN(A1)-sequence(LEN(A1))+1,1)), upper(MID(A1,LEN(A1)-sequence(LEN(A1))+1,1)))

5. Finally use functions concatenate and arrayformula to assemble reversed and case flipped characters back to the string. Here it doesn’t matter to use concatenate or arrayformula at the begining.

If you just want to reverse the order:

=ArrayFormula(concatenate(MID(A1,LEN(A1)-sequence(LEN(A1))+1,1)))

If you just want to flip the case:

=ARRAYFORMULA(concatenate(IF(exact(MID(A1,sequence(LEN(A1)),1),upper(MID(A1,sequence(LEN(A1)),1))), lower(MID(A1,sequence(LEN(A1)),1)), upper(MID(A1,sequence(LEN(A1)),1)))))

• Uncategorized