Working with text data in Excel requires precision and knowledge of the right tools. Text functions help you manipulate, combine, and format text data to meet your business needs. Whether you’re cleaning customer data, standardizing report formats, or creating dynamic text strings, mastering Excel’s text functions is essential for efficient data management.
Converting Numbers to Text with the TEXT Function
The TEXT function transforms numbers into formatted text strings, giving you control over how numerical data appears in your spreadsheets.
Basic Syntax
TEXT(value, format_code)
Here are practical examples of the TEXT function:
|
Scenario |
Formula |
Result |
|---|---|---|
|
Format number with commas |
=TEXT(1234567, “#,###”) |
1,234,567 |
|
Show 2 decimal places |
=TEXT(123.4567, “#.00”) |
123.46 |
|
Format as currency |
=TEXT(1234.56, “$#,##0.00”) |
$1,234.56 |
Date Formatting
The TEXT function excels at customizing date formats:
|
Scenario |
Formula |
Result |
|---|---|---|
|
Full date |
=TEXT(TODAY(), “mmmm d, yyyy”) |
December 17, 2025 |
|
Short date |
=TEXT(TODAY(), “mm/dd/yy”) |
12/17/24 |
|
Custom format |
=TEXT(TODAY(), “ddd, mmm d”) |
Tue, Dec 17
|
Manipulating Text with Essential String Functions
Extracting Parts of Text
LEFT and RIGHT Functions
Extract characters from the beginning or end of a text string:
|
Function |
Formula |
Example |
Result |
|---|---|---|---|
|
LEFT |
=LEFT(text, num_chars) |
=LEFT(“Customer ID”, 4) |
Cust |
|
RIGHT |
=RIGHT(text, num_chars) |
=RIGHT(“Order #12345”, 5) |
12345 |
MID Function
Extract text from the middle of a string:
=MID(text, start_position, num_chars)
|
Example |
Formula |
Result |
|---|---|---|
|
Extract middle digits |
=MID(“ABC123DEF”, 4, 3) |
123 |
|
Get month from date |
=MID(“2025-01-15”, 6, 2) |
01 |
Changing Text Case and Format
Case Conversion Functions
|
Function |
Formula |
Example |
Result |
|---|---|---|---|
|
UPPER |
=UPPER(text) |
=UPPER(“hello world”) |
HELLO WORLD |
|
LOWER |
=LOWER(text) |
=LOWER(“HELLO WORLD”) |
hello world |
|
PROPER |
=PROPER(text) |
=PROPER(“john doe”) |
John Doe |
TRIM Function
Remove extra spaces:
|
Example |
Formula |
Result 
Try the Free Spreadsheet Extension Over 500,000 Pros Are Raving About
Stop exporting data manually. Sync data from your business systems into Google Sheets or Excel with Coefficient and set it on a refresh schedule. Get Started |
|---|---|---|
|
Remove spaces |
=TRIM(” Hello World “) |
Hello World |
Combining Text Using CONCATENATE and &
Basic Text Combination
Both CONCATENATE and the & operator join text strings:
|
Method |
Formula |
Result |
|---|---|---|
|
CONCATENATE |
=CONCATENATE(“Hello”, ” “, “World”) |
Hello World |
|
& Operator |
=”Hello” & ” ” & “World” |
Hello World |
Dynamic Text Strings
Create complex combinations with cell references:
=A1 & ” – ” & B1 & ” (” & TEXT(C1, “$#,##0.00”) & “)”
Example result: “Order 12345 – John Smith ($1,234.56)”
Finding and Replacing Text
FIND Function
Locate specific characters:
|
Scenario |
Formula |
Result |
|---|---|---|
|
Find @ in email |
=FIND(“@”, “user@domain.com”) |
5 |
|
Find space |
=FIND(” “, “Hello World”) |
6 |
SUBSTITUTE Function
Replace text elements:
=SUBSTITUTE(text, old_text, new_text, [instance_num])
|
Example |
Formula |
Result |
|---|---|---|
|
Replace all |
=SUBSTITUTE(“Hello Hello”, “Hello”, “Hi”) |
Hi Hi |
|
Replace specific |
=SUBSTITUTE(“Hello Hello”, “Hello”, “Hi”, 1) |
Hi Hello |
Creating Complex Text Formulas
Real-World Examples
- Extract domain from email:
=MID(A1, FIND(“@”, A1) + 1, LEN(A1) – FIND(“@”, A1))
- Format phone numbers:
=TEXT(LEFT(A1,3),”(000)”) & ” ” & MID(A1,4,3) & “-” & RIGHT(A1,4)
- Create standardized names:
=PROPER(TRIM(CONCATENATE(A1, ” “, B1)))
Next Steps
Now that you understand Excel’s text functions, start implementing them in your spreadsheets to automate text manipulation tasks. Remember to combine functions for more complex operations and always validate your results.
Ready to take your Excel data management to the next level? Get started with Coefficient to automate your data workflows and keep your spreadsheets synchronized with your business systems in real-time.
