Tell a cell's value apart from its format, and transform text with CONCATENATE/“&”, LEFT, RIGHT, MID, and TRIM.
Format vs. value, and text functions
A format changes how a value looks, not the value
In Excel, a cell stores a value — a number, a date, text. The format only controls how it's displayed: the same number 0.25 can look like “0.25”, “25%”, or “$0.25”, depending on the format you choose. If you change the format, a formula that refers to the cell still sees the original number.
You can join text with the “&” operator or the CONCATENATE function. The formula =A1&" "&B1 joins the contents of A1, a space, and B1. =CONCATENATE(A1," ",B1) works the same way. Excel 2019 and later (and Microsoft 365) have CONCAT, which accepts whole ranges, and TEXTJOIN, which adds a delimiter between items automatically.
To extract part of a text string, use three functions. LEFT(text,num_chars) returns the first N characters. RIGHT(text,num_chars) returns the last N. MID(text,start_num,num_chars) extracts a piece starting at any position. For example, =LEFT("Moscow",3) returns “Mos”; =MID("AB-1234",4,4) returns “1234”.
TRIM removes extra spaces at the start, at the end, and between words (leaving one space between words). This matters especially when importing data: invisible spaces often stop VLOOKUP or COUNTIF from finding a match. The habit of wrapping input text in TRIM saves you from mysterious “not found” results.
Lesson notes
A format changes how a value looks, not the value
In Excel, a cell stores a value — a number, a date, text. The format only controls how it's displayed: the same number 0.25 can look like “0.25”, “25%”, or “$0.25”, depending on the format you choose. If you change the format, a formula that refers to the cell still sees the original number.
You can join text with the “&” operator or the CONCATENATE function. The formula =A1&" "&B1 joins the contents of A1, a space, and B1. =CONCATENATE(A1," ",B1) works the same way. Excel 2019 and later (and Microsoft 365) have CONCAT, which accepts whole ranges, and TEXTJOIN, which adds a delimiter between items automatically.
To extract part of a text string, use three functions. LEFT(text,num_chars) returns the first N characters. RIGHT(text,num_chars) returns the last N. MID(text,start_num,num_chars) extracts a piece starting at any position. For example, =LEFT("Moscow",3) returns “Mos”; =MID("AB-1234",4,4) returns “1234”.
TRIM removes extra spaces at the start, at the end, and between words (leaving one space between words). This matters especially when importing data: invisible spaces often stop VLOOKUP or COUNTIF from finding a match. The habit of wrapping input text in TRIM saves you from mysterious “not found” results.