Google Sheets: Sort by Second or Third character

Google Sheets Sort by Second or Third character1

This article will talk about how to sort by the second or third character in a cell’s string value in google sheets. You may want to sort the values of strings by the second or last character from the cells in google sheets, how can you do this task quickly? You can use the RIGHT function to create a google sheets formula to solve this problem. The following will show the detailed step-by-step instructions .

Using the formula to rank by the second character

To sort the test string in the cell by its second character, you can use the RIGHT function and the combination of LEN function to create a new google sheets formula, you could refer to the general formula below.

=RIGHT(Cell, LEN(Cell)-1)

This formula will remove the first character of the text string in Cell, then you can use the sort feature to sort this column in google sheets, and then the original column will be sorted as well. Please do it as below.

Suppose you want to sort the A1:A4 range in column A by the second character.

1# Select cell B1 next to cell A1 that contains the data to be sorted

Google Sheets Sort by Second or Third character1

2# Enter the following formula in the formula box in cell B1, and then press Enter.

=right(a1,len(a1)-1)

Google Sheets Sort by Second or Third character1

3# Select cell B1, then drag the AutoFill handle down to cells B2:B4 to apply this formula. You will see that the first character will be removed.

Google Sheets Sort by Second or Third character1

4# Select cell B1:B4, click the “Data” menu, and then click the “Sort sheet by column B” submenu under the “Sort sheet” menu.

Google Sheets Sort by Second or Third character1

5# You would see both column A and column B are sorted by the second character.

Google Sheets Sort by Second or Third character1

6# You can now delete column B.

Google Sheets Sort by Second or Third character1

If you want to sort by the third character, then you can use the following RIGHT formula, and then follow steps 3-7 to complete the sorting task.

=RIGHT(Cell, LEN(Cell)-2)

Related Functions

  • Google Sheets RIGHT function
    The Google Sheets RIGHT function returns a substring (a specified number of the characters) from a text string, starting from the rightmost character.The syntax of the RIGHT function is as below:=RIGHT(text,[num_chars])…
  • google sheets LEN function
    The google sheetsLEN function returns the length of a text string (the number of characters in a text string).The syntax of the LEN function is as below:= LEN(text)…    

 

 

 

 

Related Posts

Excel/Google Sheets: Absolute Reference

In Microsoft Excel spreadsheet or Google Sheets, the cell reference is the cell or cell area address or name in the worksheet, the cell reference contains two types of references, namely, relative references and absolute references. Absolute Reference An absolute ...

Conditional formatting based on another column in Google Sheets or Excel

In Microsoft Excel Spreadsheet or google sheets, conditional formatting is used to highlight any cell based on a predefined condition and the value of those cells. In the previous article, we described how to format a specific column or cell ...

Conditional Formatting based on Another Cell in Google Sheets/Excel

In Microsoft Excel Spreadsheet or google sheets, when you want to format a specified cell or cell range based on the value of a different cell, for example, formatting the first row based on the value of a cell in ...

Compare Two Strings in Excel/Google Sheets

This article will talk about how to compare two given strings in a Microsoft Excel spreadsheet or Google Sheets. How to compare two strings in Excel by using VBA macros to see if they are the same, if they are ...

Compare effect of (non-annual) compounding periods on growth

This article will talk about how to calculate the trend or effect of future value changes for different compounding periods in a Microsoft Excel spreadsheet or Google Sheets via a formula. What is the period of compound interest? The compounding ...

Sidebar