The LEFT function in Google Sheets extracts characters from the beginning of text. Use =LEFT(A2,3) for the first three characters, or change 3 to the length you need.
Extract the First Three Characters
Enter this dataset starting in A1. Leave cells marked “(blank)” empty.
| Code |
|---|
| USA-100-RED |
| IND-200-BLU |
| UK-500-WHT |
Enter this formula in E2:
=LEFT(A2,3)The result is USA. Applying the same formula to A3 returns IND; A4 returns UK-, because the hyphen counts as the third character.
LEFT Function Syntax
=LEFT(string, [number_of_characters])string is the text or cell to read. The optional character count defaults to 1. Spaces and punctuation count toward the length, so fixed lengths work best for consistent codes.
A count of 0 returns empty text. A count longer than the string returns the entire string. Negative counts return #VALUE!; decimal counts are truncated, so 2.9 extracts two characters.
For the first eight characters, use =LEFT(A2,8). Replace 8 with 5 or another required length. To extract from the opposite end, use RIGHT; for an interior portion, use MID.
Extract Prefixes for a Whole Column
=ARRAYFORMULA(LEFT(A2:A4,3))Enter this in an empty output column. It spills USA, IND, and UK- down three rows. Leave the output cells empty so the array can expand.
Get an Initial or the First Name
Enter this dataset starting in A12. Leave cells marked “(blank)” empty.
| Name |
|---|
| Maya Stone |
| Noah |
| Ari Lee |
=LEFT(A13) returns M. Omitting the character count is equivalent to requesting one character. For everything before the first space, use:
=LEFT(A13,FIND(" ",A13)-1)The result is Maya. FIND locates the first space; subtracting 1 excludes that space from the extracted text. This method takes the first word, which may differ from a complete given name.
A single name such as Noah has no space. Use this fallback to return the whole string when FIND cannot locate the delimiter:
=LEFT(A14,IFERROR(FIND(" ",A14)-1,LEN(A14)))If names contain leading spaces, clean them before locating the first word:
=LEFT(TRIM(A15),FIND(" ",TRIM(A15))-1)That returns Ari. The same approach works with a hyphen: =LEFT(A4,FIND("-",A4)-1) returns UK. Use SPLIT if you need every segment in separate cells.
Use a Different Character Count for Each Row
Enter this dataset starting in A22. Leave cells marked “(blank)” empty.
| Text | Characters |
|---|---|
| Spreadsheet | 6 |
| Productivity | 4 |
| Formula | 1 |
=LEFT(A23,B23)This returns Spread. Fill down two rows to get Prod and F. Column B controls each row independently, which helps when records have different prefix lengths.
Flag Rows by Their Prefix
=IF(LEFT(A2,2)="US","US Code","Other")A2 returns US Code; the other two codes return Other. The ordinary equals comparison is not case-sensitive. Use an exact case comparison if uppercase and lowercase prefixes have different meanings.
Other Google Sheets articles you may also like