# Text Manipulation Formulas in Excel – The Ultimate Guide When you are cleaning up data in Excel, the Text-to-Columns and Flash Fill features are awesome, but sometimes you need to use formulas to manipulate text. In this article I'll demonstrate examples of the text formulas I commonly use, including LEN, TRIM, UPPER, LOWER, PROPER, CONCATENATE, INDIRECT, CHAR, FIND, SEARCH, SUBSTITUTE, LEFT, RIGHT, MID and REPLACE (and some others).

The examples start very simple and then get progressively more advanced as you scroll through the page, building upon earlier examples.

Do you have a text manipulation challenge that you need a formula to solve? Feel free to ask your question by commenting below.

## 1. Get the LENgth of a text string

```=LEN("onetwothree")
Result: 11```

This comes in handy when you need to write a title for a web page or complete a form with a limited number of characters. Just open a blank spreadsheet and type your title in cell A1. In B1, enter =LEN(A1).

## 2. Change case to UPPER, lower, or Proper

```=UPPER("this text")   Result: THIS TEXT
=LOWER("THIS TEXT")   Result: this text
=PROPER("this text")   Result: This Text```

## 3. Concatenate a text string

You can use CONCATENATE, the & operator, or the newer CONCAT and TEXTJOIN functions to concatenate strings. The following formulas combine a first name in cell A1 and a last name in cell B1 with a space in the middle. The result is "John Smith" for all four formulas.

```A1="John"
B1="Smith"

=A1 & " " & B1
=CONCATENATE(A1," ",B1)
=CONCAT(A1:B1)
=TEXTJOIN(" ",TRUE,A1:B1)

Result: "John Smith"
```

NOTE The spaces before and after the & operator are not required - I've included the spaces only to help make the formula more readable.

The CONCAT and TEXTJOIN functions are new functions that requires an Office 365 subscription (they work in Excel Online). The CONCAT function is like CONCATENATE except that it lets you use a range of cells as an argument. The TEXTJOIN function lets you specify a delimiter and ignore blank values.

## 4. Use INDIRECT to create a reference from a text string

The INDIRECT function allows you to create a reference from a text string. The example below shows a reference to cell A5 in worksheet 'Sheet 2'. The single quotes around the worksheet name are only necessary if the worksheet name includes a space.

`=INDIRECT("'Sheet 2'!A5")`

Use INDIRECT if you want the worksheet name to be a text string chosen by the user. For example, you may want to do this if you have many identical worksheets and you want to create a summary table that uses the names of those worksheets as references in your lookup formulas.

The following example creates a reference to cell X5 in a worksheet that is named in cell A1.

```A1="Sheet 2"
A2=INDIRECT("'" & A1 & "'!X5")
```

The INDIRECT function can be very useful in array formulas. For example, to create an array of numbers 1 through N, where N is a number contained in cell A1, you can use:

```=ROW(INDIRECT("1:" & A1))
```

## 5. Use CHAR to return special characters

The CHAR function lets you return a character for a given numeric code. The UNICHAR function returns a character for a decimal Unicode value. Although most of the numeric codes for the CHAR function correspond to the ASCII codes, some may not be the same (such as codes 128-160).

The functions CODE and UNICODE are the opposites of CHAR and UNICHAR, returning the numeric value for the first character in a text string.

### Use CHAR(34) to return the double quote " character

When you concatenate text and need to include double quotes in the displayed text, you can use the CHAR(34) or UNICHAR(34) function. Both the ASCII and Unicode value for double quotes is 34.

```=CHAR(34) & "Hi World" & CHAR(34)
Result: "Hi World" (quotes included)```

### Use CHAR(10) to include a line break in a string

When using a formula to return a string, use CHAR(10) or UNICHAR(10) for a line break. See Custom Number Formats to learn how to add a line break within a custom number format (for chart labels and stuff like that).

```="abc" & CHAR(10) & "def"
Result:
abc
def
```

! To display wrapped text with line breaks, the cell must also have the Word Wrap property toggled on.

TIP To quickly generate a list of characters based on their numeric code, enter =CHAR(ROW()) or =UNICHAR(ROW()) into cell A1 of a blank worksheet and copy the formula down.

## 6. SUBSTITUTE text within a string

The SUBSTITUTE function is very powerful. It can be used to replace ether ALL occurrences or just the Nth occurrence of a string with another character or text string. In the example below, we're replacing the # character with a space.

```text = "one#two#three"
=SUBSTITUTE(text,"#"," ")   Result: "one two three"
=SUBSTITUTE(text,"#"," ",2)   Result: "one#two three"
```

## 7. Use TRIM to get rid of extra spaces

The TRIM function removes all regular spaces (ASCII character 32) except for a single space between words.

```=TRIM("   Hi  World  ")
Result: "Hi World"  (quotes not included)```

TRIM does not remove tabs, line breaks, or other nonprinting characters from the text. To remove the non-printing ASCII characters 0-31 (including the tab character), you can use the CLEAN function.

```text="Hi		World" (contains two tabs)
=CLEAN(text)
Result: "HiWorld"```

The problem with the CLEAN function is that it completely removes the characters, so words separated by tabs or newline characters will be combined, so you may end up with "HiWorld" when you would prefer "Hi World".

To change special characters to regular spaces, you can use the SUBSTITUTE function and then wrap the function with TRIM to remove extra spaces like this:

```text="Hi		World" (contains two tabs)
=TRIM( SUBSTITUTE(text,CHAR(9)," ") )
Result: "Hi World"```

NOTE Here is a short list of CHAR codes for commonly replaced characters: Tab (9), Newline (10), Carriage Return (13), Space (32), Non-Breaking Space (160), Special Quote Symbols: ‘(145), ’(146), “(147), ”(148)

## 8. Use FIND and SEARCH to get the position of text in a string

The FIND function for case-sensitive searching and the SEARCH function for case-insensitive searching will return the starting character position of a text string within another string.

```=FIND("a","ooAooaoo",1)   Result: 6
=SEARCH("a","ooAooaoo",1)   Result: 3
```

The 3rd argument of the FIND and SEARCH functions is the starting character position to begin the search, with the default being 1 (the first character). You can use a nested FIND or nested SEARCH to find the position of the 2nd occurrence of a text string like this:

```text="ooAooAoo"
=FIND("A",text,FIND("A",text,1)+1)

Evaluation Steps
Step 1: FIND("A","ooAooAoo",3+1)
Step 2: FIND("A","ooAooAoo",4)
Step 3: 6```

You can do some really tricky things with SUBSTITUTE when combined with FIND or SEARCH. The following function allows you to find the location of the Nth occurrence of a string within another text string. In this example, we want to know the position of the 3rd space in the name.

```text="Tim A. J. Crane"
=FIND("#",SUBSTITUTE(text," ","#",3),1)

Evaluation Steps
Step 1: =FIND("#","Tim A. J.#Crane",1)
Step 2: =10
```

## 9a. Use MID, LEFT and RIGHT to extract text from a string

The MID function is like the substr() function in other coding languages. It extracts a string from within another string by specifying the starting character position and the number of characters to extract. The REPLACE function is similar except that it returns the original text string with the text replaced. The LEFT and RIGHT functions are like shorthand versions of MID for extracting text from the left or right end of a string.

```SYNTAX: =MID(text,start_num,num_chars)
SYNTAX: =REPLACE(text,start_num,num_chars,replace_text)
SYNTAX: =LEFT(text,num_chars)
SYNTAX: =RIGHT(text,num_chars)```

Below is an example showing how these functions work.

```text = "one#two#three"
=MID(text,5,3)   Result: "two"
=REPLACE(text,5,3,"BLAHBLAH")   Result: "one#BLAHBLAH#three"
=LEFT(text,5)   Result: "one#t"
=RIGHT(text,7)   Result: "o#three"
```

I haven't had much use for the REPLACE function, because I typically use SUBSTITUTE instead of REPLACE.

The MID, LEFT, and RIGHT functions become much more powerful when you use the FIND or SEARCH functions within them. Some of the following tips show examples of that.

## 9b. Use the New TEXTAFTER and TEXTBEFORE functions!

New text functions are coming soon to the latest version of Excel. Like their names suggest, TEXTAFTER and TEXTBEFORE let you extract text after or before the nth instance of a delimiter. Some of the complex stuff done with the MID function can be done more easily with these new functions.

```SYNTAX: =TEXTAFTER(text,delimiter,[n],[ignore_case])
SYNTAX: =TEXTBEFORE(text,delimiter,[n],[ignore_case])
```

Using the text from the MID function above, here are a couple examples:

```text = "one#two#three"
=TEXTAFTER(text,"#",2)   Result: "three"
=TEXTBEFORE(text,"#",2)   Result: "one#two"
```

If you enter a negative value for n, it will count the instance from the end of the string. This will come in handy when you need to extract the last part of the string but you don't know how many delimiters there will be.

## 10. Count the number of spaces in a text string

You can use this technique to count other characters besides spaces. For example, just substitute " " with "," or ";" to count the number of commas or semi-colons.

```text = "Todd Allen Smith"
=LEN(text)-LEN(SUBSTITUTE(text," ",""))

Evaluation Steps
Step 1: LEN("Todd Allen Smith")-LEN("ToddAllenSmith")
Step 2: 16-14
Step 3: 2
```

The SUBSTITUTE function in this example returns a new text string with the spaces removed (replacing all " " with ""). We are subtracting the length of that modified text string from the original length to calculate the number of spaces in the original text.

## 11. Count occurrences of a string within text

If you want to count the number of occurrences of a string within text (instead of just a single character), then you can use a slightly modified version of the above formula. In this case, we'll just divide the result by the length of string.

```text = "A##B##C"
string = "##"
=(LEN(text)-LEN(SUBSTITUTE(text,string,""))) / LEN(string)
Result: 2```

## 12. Split text into columns using formulas

The Text-to-Columns Wizard and Flash Fill (Ctrl+e) features in Excel are fast and simple to use, but there may be times when you want to use formulas instead (to make a more dynamic or automated worksheet). Splitting up text using formulas typically involves a combination of LEFT, RIGHT, MID, LEN, and FIND (or SEARCH). We'll start with a couple simple formulas.

### Extract the First Name

To extract the first word (or name) from a text string, you can use the following formula, where text is either a cell reference or a string surrounded by double quotes like "this".

```text = "Tom Sawyer"
=LEFT(text,FIND(" ",text)-1)

Evaluation Steps
Step 1: =LEFT("Tom Sawyer",4-1)
Step 2: =LEFT("Tom Sawyer",3)
Step 3: ="Tom"```

In the above formula, FIND(" ",text) returns the numeric position of the first space " " within the text. We subtract one from that value so the space is not included in the result.

The new TEXTBEFORE function is easier (the default for the instance number is 1):

```=TEXTBEFORE("Tom Sawyer"," ")  Result: "Tom"
```

### Extract the Text After the First Space

To return the rest of the string after the first space, we use the RIGHT function, which extracts a specified number of characters from the end of the string. We calculate the number of characters to extract by subtracting the position of space from the total length of the string:

```text = "Jay Allen Reems"
=RIGHT(text,LEN(text)-FIND(" ",text))

Evaluation Steps
Step 1: =RIGHT("Jay Allen Reems",LEN("Jay Allen Reems")-4)
Step 2: =RIGHT("Jay Allen Reems",15-4)
Step 3: =RIGHT("Jay Allen Reems",11)
Step 4: ="Allen Reems"
```

The new TEXTAFTER function is easier (the default for the instance number is 1):

```=TEXTAFTER("Jay Allen Reems"," ")  Result: "Allen Reems"
```

We could repeat these formulas in other columns to extract Allen and then Reems.

The article "Split text into different columns with functions" on support.office.com provides various examples of formulas for separating names into different parts based on different ways that a name may be written.

### The SPLIT function in Google Sheets

I hope that Excel eventually includes a SPLIT function like the one available in Google Sheets. For example, to split a name like "Allen James Reems" into separate cells only requires the following simple formula:

`=SPLIT(text," ")`

### New TEXTSPLIT function in Excel!

The new TEXTSPLIT function will soon be available in the latest version of Excel. It isn't exactly the same as the Google Sheets SPLIT function. It's better in my opinion because it can split text into a row, column, or two-dimensional dynamic array.

```SYNTAX: =TEXTSPLIT(text,[column_delimiter],[row_delimiter],[ignore_empty],[pad_width])
```

## 13. Get the last word in a string

For this example, we'll use the name "Allen Jay Reems" to show how to get the last word in a string, where a space character is the delimiter.

This will soon be very easy with the TEXTAFTER function (see 9b above):

```=TEXTAFTER("Allen Jay Reems"," ",-1)   Result: "Reems"
```

The following example shows how to do this if TEXTAFTER is not available, and also shows how I sometimes build a more complicated formula using intermediate steps.

```delimiter = " "
last_name
=RIGHT(text,LEN(text)-position_of_last_delimiter)
position_of_last_delimiter
=FIND("^", SUBSTITUTE(text,delimiter,"^",number_of_delimiters))
number_of_delimiters
=LEN(text)-LEN(SUBSTITUTE(text,delimiter,""))
```

The final formula looks like this with A1="Allen Jay Reems" and will return the last name "Reems":

```=RIGHT(A1,LEN(A1)-FIND("^", SUBSTITUTE(A1," ","^",LEN(A1)-LEN( SUBSTITUTE(A1," ","") ))))
```

If you have a string delimited by commas like "one, two, three, four" you can extract the last element by replacing " " with "," in the above formula and wrapping the entire thing with TRIM to remove the leading space.

If your string might not contain any spaces, then you can wrap the entire formula with IFERROR to return an empty string or the original text.

If your string contains the "^" character, you'll need to choose a different temporary delimiter to use in the formula such as "~" or another uncommonly used character.

## 14. Get the Nth word in a string

This is another task that will be much simpler with the new TEXTBEFORE function. Using the example below:

```=TEXTBEFORE(TEXTBEFORE("One#Two#Three",2),-1)
Evaluation Steps
1: =TEXTBEFORE("One#Two",-1)
2: ="Two"
```

Without the new functions, this formula is really crazy, but still useful. I learned it from a post on mrexcel.com. Basically what is going on is that you replace the delimiter text with a bunch of blank spaces so that you create a new text string that can be divided into chunks, where each chunk contains a different word. There will be a lot of space surrounding each word, so you use TRIM to remove it.

```text = "One#Two#Three" (the original text)
delimiter = "#" (the delimiter text)
word_num = 2 (the word to extract)

=TRIM(MID(SUBSTITUTE(text,delimeter,REPT(" ",LEN(text))),(word_num-1)*LEN(text)+1,LEN(text)))

Evaluation Steps
1: =TRIM(MID(SUBSTITUTE(text,"#",REPT(" ",13)),(2-1)*13+1,13))
2: =TRIM(MID(SUBSTITUTE(text,"#","             "),14,13))
3: =TRIM(MID("One             Two             Three",14,13))
4: =TRIM("  Two        ")
5: ="Two"
```

In Google Sheets, this is a piece of cake. The SPLIT function returns an array, so you can return the 3rd word in a string using:

`=INDEX(SPLIT(text,delimiter),3)`

## 15. Convert a text string to an array of words

Want to convert "One#Two#Three" into an array like {"One";"Two";"Three"} that can be used within other formulas? That is what the SPLIT function in Google Sheets does, but to do this in Excel is still possible - it's just complicated. First, start with the formula in the previous section and replace word_num with the following:

```=ROW(INDIRECT("1:"&((LEN(text)-LEN(SUBSTITUTE(text,delimiter,"")))/LEN(delimiter)+1)))
```

To display the results within an array of cells, remember to use Ctrl+Shift+Enter. Use TRANSPOSE if you want to display the results of this formula in a row instead of a column.

To create the array as an inline text string, you can use the following formula:

```text = "One#Two#Three" (the original text)
str = "#" (the delimiter text)
="{"&CHAR(34)&SUBSTITUTE(text,str,CHAR(34)&";"&CHAR(34))&CHAR(34)&"}"

Resulting text string: {"One";"Two";"Three"}
```

## 16. Convert a text string to an array of characters

If you want to split a text string into an array of individual characters, such as converting "abcd" to {"a";"b";"c";"d"}, the formula is fairly simple. This formula is entered as an Array Formula (Ctrl+Shift+Enter).

`=MID(text_string,ROW( INDIRECT("1:"&LEN(text_string)) ),1)`

To convert each of the characters in a string to their numeric codes, wrap the above function with CODE or UNICODE. The formula would be entered as a multi-cell array to display each of the numeric values in a different cell.

The following formula in Google Sheets will convert a text string to a comma-delimited list of numeric code values.

```text="Hello"
=ARRAYFORMULA( TEXTJOIN(",",TRUE, CODE(MID(text,ROW( INDIRECT("1:"&LEN(text)) ),1))) )
Resulting text string: "72,101,108,108,111"
```

## 17. Use EXACT for case-sensitive text comparisons

If you ever need to determine if the text in a cell is UPPERCASE, lowercase, or Proper Case, you can use the EXACT formula to compare the original text to the converted text.

The following formulas return TRUE if the text in cell A1 is uppercase, lowercase or proper case, respectively:

```=EXACT(A1,UPPER(A1))
=EXACT(A1,LOWER(A1))
=EXACT(A1,PROPER(A1))```

Note: The SUMIF and COUNTIF article provides a lot of different examples of text-based comparisons.

## Have a Text Formula Challenge?

If this article hasn't answered your question, feel free to comment below if you have a problem that you want solved using a text formula. Make sure to provide sufficient detail for your question to be answered. Thank you!

• I have a cell that generates a plus over minus sign as the first character from real time data. I need to work with the number following the plus over minus sign. How do I extract that value in a formula?

• Depends whether the ± character has been added via a custom number format, or whether the result is a text value. If it’s text, then you could use SUBSTITUTE to remove the ± character and VALUE to convert the value to a number.

• I’m trying to extract the initials from a last name where the last name may be hyphenated or have a space. Examples Robin Van Bout, James Taylor-Wong … I have this formula which I can only get to work for one but not both space or hyphen.
=IF(OR(IFERROR(FIND(” “, D9)>1,),IFERROR(FIND(“-“,D9)>1,),),LEFT(E9,1)&LEFT(D9,1)&MID(D9,FIND(” “,D9)+1,1),LEFT(E9,1)&LEFT(D9,1)). How would I search for both spaces or hyphens in the last name? I’ve tried various combinations of the OR function with FIND, but with no luck. The first name is in E9 and the last in D9.
Thanks

• @Jean, one idea would be to use SUBSTITUTE to change all hyphens to spaces first.

• Hi. I have to split text from one column into two new. Maximum lenght in first new column is 14 chars. Words from source column can not be split. If the word is split should transfer whole word to second column. How to do this?

• Hi. I have a text challenge that I need help please. Say, I have a string of keywords (search string) that I am going to use to search in health databases:

health literacy, information literacy, health education, health education, patient education, patient information

Using the substitute function, the search string was converted to:

health literacy OR information literacy OR health education OR health education OR patient education OR patient information

My current problem is that, I would like to automate so that if there are two or more before the OR (or the comma as delimiter), these words should be enclosed in ” ” (quotation marks).

Any tips, help with my challenge are greatly appreciated. Thank you!

• If your original text was in cell A1: =CHAR(34)&SUBSTITUTE(A1,”, “,CHAR(34)&” OR “&CHAR(34))&CHAR(34)

• i need the formula to get the first 3 capital letters before “-”

sample data:

GFYJHG-def109 =result should be JHG
-fKILo-dRek l = result should be KIL
9jH PuL-9op = result should be HPL

• I’d do that in stages (multiple columns), to check that the data is clean after each stage.
Step 1: Remove everything to the right of the dash B1=LEFT(A1,FIND(“-“,A1,2)-1) (Having multiple dashes could mess this up, but the “2” in the FIND function ignores the first character being a dash.
Step 2: Remove every character OTHER than the ones you want (not robust unless you have a limited number of possible characters in these strings). This is a massive nested SUBSTITUTE function:
C1=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B1,”a”,””),”b”,””),”c”,””),”d”,””),”e”,””),”f”,””),”g”,””),”h”,””),”i”,””),”j”,””),”k”,””),”l”,””),”m”,””),”n”,””),”o”,””),”p”,””),”q”,””),”r”,””),”s”,””),”t”,””),”u”,””),”v”,””),”w”,””),”x”,””),”y”,””),”z”,””),”*”,””),”-“,””),” “,””),”1″,””),”2″,””),”3″,””),”4″,””),”5″,””),”6″,””),”7″,””),”8″,””),”9″,””),”0″,””)
Step 3: Grab the last 3 characters: D1=RIGHT(C1,3)

• Data Formula Result to be
B05051602404401 =MID(A2,2,10) 0505160240
BS05051602404401 =MID(A3,3,10) 0505160240
HW05051602404401 =MID(A4,3,10) 0505160240
P05051602404401 =MID(A5,2,10) 0505160240
PSW05051602404401 =MID(A6,4,10) 0505160240

I have around 200,000 data points with different text, needs to code according to position of the text .

Thank you

• I have a column of data which shows different health insurance plans. It might look something like

BCBS- TN
BCBS- OH
BCBS- AL
Medicare- MS
UNITED HEALTHCARE COMMUNITY PLAN – MSCAN (MEDICAID HMO)
UMR – UNITED HEALTHCARE
CENTENE – MAGNOLIA HEALTH PLAN (MEDICAID REPLACEMENT – HMO)
BCBS-KY: ANTHEM BCBS OF KY BLUE ACCESS (PPO)
MEDICAID-MS (MEDICAID)

I’d like to categorize each of these into broad categories of the insurance company eg. blue cross/blue shield; United; Medicare; Medicaid, etc. Then the remainder of the data be used to subcategorize the type of insurance so that I can pull the data into a pivot table.

• You could try using the FIND and SEARCH functions within your formulas (if the short and long dashes are used to break the data into broad / sub categories). Whether you create a formula to do that, or use other methods for separating and sorting your data depends on whether you want the solution to be dynamic or whether this is just a one-time effort (and the Flash Fill technique might help if this is a one-time thing). You could ask ExcelRescue.net for a quote if you want more specific help.

• Hi, i am having a problem using spell number formula and trying to fill it in 2 different rows. For example i want the numbers spelt out until it reach a certain length then go directly underneath the row on top without using merge and center because i do not want to adjust the column size every time. Could you please assist me with this? I dont know much about advance formulas and coding. Thank you

• Is there a formula that will convert the following:

Jane R. smith to Smith, Jane R
or
john E. Doe, Jr to Doe Jr, John E

• Hello, Jon –

I am importing a .csv file (my only option) with this date format: 2022-11-30T14:40:02-0600

I would like it to look like this: Nov 30, 2022 14:40:02

I have tried multiple steps with multiple functions, but have not found a solution. Any help would be appreciated.

• I have challenge in entering a list of names with Initial Surname. I use:
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Application.EnableEvents = False
If Target.Column = 3 Then
Target = StrConv(Target, vbProperCase)
End If
Application.EnableEvents = True
End Sub

to automatically change the entered names to proper case. However, how can i enable the code to handle:

McLean (displays as Mclean)
Walker-Davis (displays as Walker-davis)

• How do I get excel to display a specific phrase based off of data in multiple rows.

Ie. B2 -, C2 +, D2 + then cell E2 would default to Immune

I would like to add multiple formulas that reads the + or – information inputted and then it essentially displays the correct status that I assign it (immune, susceptible, acutely infected, etc.) in the next row.

Thank you,

Charles

• Likely a combination of concatenation and the IF function?

• I am trying to convert some branches and their addresses including the zip code from 1 whole text to several columns but can’t figure out why I can’t do it text to columns … can you help? Sample date : TM SAN FABIAN PANGASINAN
BARANGGAY CAYANGA SAN FABIAN PANGASINAN, Pangasinan, 2433