Extracte Text From String
| Data | Formula | Result | Description |
|---|---|---|---|
| 12345-6789 | =LEFT(A2,5) |
12345 | Convert nine-digit postal codes into five-digit postal codes |
| (888)123-4567 | =RIGHT(A3,8) |
123-4567 | Extract phone numbers without the area code |
| P3X245 | =MID(A4,3,1) |
X | Extract the 3rd character of the string |
Join text strings
| Text1 | Text2 | Text | Result | Description |
|---|---|---|---|---|
| Chris | McMullen | =A2&" "&B2 |
Chris McMullen | Use the ampersand (&) operator |
| Chris | McMullen | =CONCATENATE(A3," ",B3) |
Chris McMullen | Combine text |
| Micro | soft | =CONCAT(A4:B4) |
Microsoft | Combine range of cells |
| Chris | McMullen | =TEXTJOIN(" ",TRUE,A5:B5) |
Chris McMullen | Combine text in an easier way |
Set text to sentence case
| Data | Formula | Result | Description |
|---|---|---|---|
| knowledge is power | =UPPER(A2) |
KNOWLEDGE IS POWER | Convert text to all uppercase |
| KNOWLEDGE is power | =LOWER(A3) |
knowledge is power | Convert text to all lowercase |
| knowledge is power | =PROPER(A4) |
Knowledge Is Power | Convert text to title case |
| knowledge is power | =UPPER(LEFT(A5,1))&RIGHT(A5,LEN(A5)-1) |
Knowledge is power | Convert text to sentence case |
Remove spaces from a text string
| Data | Formula | Result | Description |
|---|---|---|---|
| Excel 365 | =TRIM(A2) |
Excel 365 | Remove leading spaces |
| Excel 365 | =TRIM(A3) |
Excel 365 | Remove middle spaces |
| Excel 365 | =TRIM(A4) |
Excel 365 | Remove trailing spaces |
| Excel?365 | =TRIM(SUBSTITUTE(A5,CHAR(160)," ")) |
Excel 365 | Remove nonbreaking space |
Find specific characters
| Data | Formula | Result | Description |
|---|---|---|---|
| AB-123-456 | =FIND("-",A2) |
3 | Find first hyphen |
| AB-123-456 | =FIND("-",A3,FIND("-",A3)+1) |
7 | Find second hyphen |
| AB-123-456 | =RIGHT(A4,LEN(A4)-FIND("-",A3,FIND("-",A3)+1)) |
456 | Extract everything after the second hyphen |
| AB-123-4567 | =RIGHT(A5,LEN(A5)-FIND("-",A4,FIND("-",A4)+1)) |
4567 | Extract everything after the second hyphen |
Substitute text strings
| Data | Formula | Result | Description |
|---|---|---|---|
| BEN'S COFFEE | =SUBSTITUTE(PROPER(SUBSTITUTE(A2,"'","zzzzz")),"zzzzz","'") |
Ben's Coffee | Fix the apostrophe S issue |
| A B C D | =LEN(A3)-LEN(SUBSTITUTE(A3," ","")) |
3 | Count space character in a cell |
| Big Big World | =LEN(A4)-LEN(SUBSTITUTE(A4," ",""))+1 |
3 | Count words in a cell |
Add a line break in formula
| Data | Formula | Result | Description |
|---|---|---|---|
| Ben | =A2&CHAR(10)&2023 |
Ben 2023 |
Use line breaks in chart x-axis labels |
* View this result in Format Cells > Alignment > Wrap text
Format the numbers
| Data | Formula | Result | Description |
|---|---|---|---|
| 50 | =TEXT(A2,"#"" lbs""") |
50 lbs | Add any text |
| 12345 | =TEXT(A3,"00000") |
12345 | Zip Code |
| 123456789 | =TEXT(A4,"00000-0000") |
12345-6789 | Zip Code + 4 |
| 1234567899 | =TEXT(A5,"[<=9999999]###-####;(###) ###-####") |
(123) 456-7899 | Phone Number |
| 123456789 | =TEXT(A6,"000-00-0000") |
123-45-6789 | Social Security Number |
Learn more about TEXT function
Clean strange characters
| Data | Formula | Result | Description |
|---|---|---|---|
| =50&CHAR(12)&" Pt" | =CLEAN(A2) |
50 Pt | Removes the first 32 nonprinting characters |
| ="Excel"&" 2023"&CHAR(12) | =TRIM(CLEAN(A3)) |
Excel 2023 | Removes the first 32 nonprinting characters and excess spaces |