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.
Download the Example File (TextFormulas.xlsx)
Do you have a text manipulation challenge that you need a formula to solve? Feel free to ask your question by commenting below.
Links to Text Formulas on this Page
- 1. Get the LENgth of a text string
- 2. Change case to UPPER, lower, or Proper
- 3. CONCATENATE a text string
- 4. Use INDIRECT to create a reference from a text string
- 5. Use CHAR to return special characters
- 6. SUBSTITUTE text within a string
- 7. Use TRIM to get rid of extra spaces
- 8. Use FIND and SEARCH to get the position of text in a string
- 9a. Use MID, LEFT and RIGHT to extract text from a string
- 9b. New TEXTAFTER, TEXTBEFORE functions
- 10. Count the number of spaces in a string
- 11. Count occurrences of a string within text
- 12. Split text into columns using formulas
- 13. Get the last word in a string
- 14. Get the Nth word in a string
- 15. Convert a string to an array of words
- 16. Convert a string to an array of characters
- 17. Use EXACT for case-sensitive text comparisons
- 18. Create a SEQUENCE of characters
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.
See my article "Using UNICODE Characters in Excel" for more information.
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(num) can also be a useful way to convert a number to text without any special formatting, or to wrap a formula to make sure that the result of a text formula remains text.
=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)
Two LAMBDAs for Special Clean Functions
The following lambda function first changes tabs (9), newlines (10), and non-breaking spaces (160) to regular spaces, then uses CLEAN to remove other nonprinting characters, and finally TRIM to remove extra spaces.
textCleanPlus =LAMBDA(text, TRIM(CLEAN(SUBSTITUTE( SUBSTITUTE( SUBSTITUTE(text,CHAR(160)," "),CHAR(10)," "),CHAR(9)," ") )) )
The following lambda function replaces the special single quote (145) and (146) with a regular single quote and special double quotes (147) and (148) with regular double quotes.
textFixSpecialQuotes =LAMBDA(text, SUBSTITUTE( SUBSTITUTE( SUBSTITUTE( SUBSTITUTE(text,CHAR(145),"'"),CHAR(146),"'"), CHAR(147),CHAR(34)), CHAR(148),CHAR(34)) )
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
If you find yourself doing this a lot, you can create a LAMBDA function to return the count of instances of str within text.
LAMBDA for Office 365! V42_COUNTCHAR(text,str) = LAMBDA(text,str, (LEN(text)-LEN(SUBSTITUTE(text,str,""))) / LEN(str) )
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 is available in Microsoft 365. 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])
You can read more about TEXTSPLIT at MyOnlineTrainingHub.com.
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.
In Microsoft 365, this is very easy to do 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 a task that is much simpler with the new TEXTSPLIT function. TEXTSPLIT will convert a text string into an array based on a delimiter. INDEX can then pick the nth value.
=INDEX(TEXTSPLIT(text,,delimiter),n) =INDEX(TEXTSPLIT("One#Two#Three",,"#"),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.
18. Create a SEQUENCE of characters starting with a letter
Using the new SEQUENCE function in Office 365 makes it easy to create a sequence or array of characters because you first convert the starting character to its unicode value using CODE or UNICODE, then create the sequence, then convert that sequence back to characters using CHAR or UNICHAR.
In Office 365! =CHAR( SEQUENCE(5,,CODE("C")) ) Resulting array: "C","D","E","F","G"
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!
Comments
thank you for sharing !!!
I have a list of names where the parts of the names are all jumbled up! I need to arrange the names in a particular order so that the number of unique names can be identified and then we can apply the VLOOKUP formula for further calculations.
The name Juãn Carlos Da Silva would appear either as Juãn Carlos DA SILVA or DA SILVA Juãn Carlos etc.
The name J. Morrison would appear either as J. MORRISON or MORRISON J.
There are hundreds of thousands of rows with about 10000 unique names (may be more!), each form of the name appearing multiple times! The following are the properties I could locate myself:
1. Each name would have at least 1 first name and 1 last name.
2. Each name has 0 to 4 middle names.
3. Each part of the name, the first, last as well as the middle name(s), has at least 2 characters.
4. The last name is always in Upper Case.
5. The first name is always in Proper Case or only the first letter in upper case followed by a “.” (dot).
6. The middle name(s) may be either in Proper Case or in Upper Case.
7. Each part of the name may contain the Latin-1 supplement characters like ã, õ, ù, Á, Ç, Ü etc, as is there in the example given above.
8. Each part of the name may contain “-” (hyphen).
9. The last name does not have the “.” (dot).
10. The middle name(s) may have the “.” (dot) or also the “-” (hyphen).
11. The “.” (dot), if it is there, is at the end of the first or a middle name.
12. No name has more than one “.” (dot).
13. The first letter of each name is an upper case letter.
14. The proper case words in the name appear next to each other, either at the beginning or at the end. So are the upper case words in the name.
Need a formula to convert the names to make them appear in one particular form. Using code is not an option. Macro is disabled as of now! Formula compatible with Excel 2007 would be much appreciated, however formula compatible with Excel 2010 would still work!
@Michael … The answer to your question might require a full article to explain in detail. Without Googling to see if there is a more elegant solution, I’d probably tackle the problem by first splitting the names into columns (using the Text to Columns feature).
Then, sort the names in each row in alphabetical order so that you could more easily compare duplicates. In other words, you want “J. C. MORRISON” and “MORRISON J. C.” to both become “C. J. MORRISON” so that you can later concatenate the names into a single column to use for identifying duplicates.
To make this a piece of cake, I would copy or upload the list of names into Google Sheets because of the awesome SORT function. If your split names are located in columns A-D, then the following formula copied down in column E would create a new set of columns with each row sorted alphabetically: =TRANSPOSE(SORT(TRANSPOSE(A2:D2),1,TRUE))
This is just a method for getting the data into a form that can be used to easily identify duplicates. If you need additional help, I would recommend contacting an expert Excel consultant to help you.
Hi Jon,
Thanks for the response. Also, let me apologize for not stating the problem more precisely!
I already have a set of data with about 36 columns (Column A through AJ). The names appear in the 1st or the leftmost column A. I need to get the “formatted name” in an inserted column in each of the rows. There are and will be multiple occurrence of the same name in multiple rows. But if the names are in the same format, it would be easy and accurate to prepare the reports using VLOOKUP and Pivot Table and other standard Excel formula. Presently, because of the ways the names appear, VLOOKUP and Pivot Table are considering the same person as two or more different people!
Also, (not sure if this changes the significance of the problem in any manner) let me point out that the kind of name you suggested in your response, C. J. Morrison isn’t there in the list, as it has 2 “.” (dot) characters. As I mentioned earlier, in point number 12, there’s at most 1 “.” (dot) in a name and that isn’t in the last name (mentioned in point 9).
That’s fine. Just insert a bunch of columns as needed so that you can work with the names. Or temporarily copy the names into a separate spreadsheet so that you can do the manipulation (making sure to preserve the row order).
Good Morning,
I was wondering if there was a formula to convert numbers as follows…
#####.#### with no decimal
4 twould need to read 000040000
1.20 would need to be 000012000
12.3666 to 000123666
Any advice would be greatly appreciated.
Thank you, Krista
@Krista, I would probably use the formula =value*10000 and then use a Custom Number Format of 000000000. Or, you could use the formula =TEXT(A1*10000,”000000000″) where your list of numbers starts with cell A1. The result of the TEXT formula will be text (not a number), but maybe that’s what you want.
Hi!
I was wondering if there’s a formula to mask certain letters, for example:
John Henry —-> J**n H**ry
Adams Sandler —-> *da*m* S*an*l**
The sequence of masking doesn’t matter, so long that the full name is not revealed. I have multiple entries and is seeking for a formula to simplify this task. Greatly appreciate your help !!
Regards,
Yvonne
Does that really help security at all? It seems like it would be pretty easy to guess what the names are. If you want to mask specific letters, then SUBSTITUTE would be helpful, and may be the simplest solution if you just mask all vowels. For example: SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,”a”,”*”),”e”,”*”),”i”,”*”),”o”,”*”),”u”,”*”),”y”,”*”)
Hi- I am familiar with excel, but I only at a basic level. I have started collecting data on a spreadsheet. It is formatted as a table and I have filters and a few pivot tables, but now my boss wants to see this data in a different summary format. For example, I have listed all our shipments details such as: vendor, customer, destination, shipping cost, , etc.; he wants to be able to just filter by customer name, or port, or vendor, and be able to get the high and low cost of shipping. I can do this by filtering the table, but I think he wants a format that he can use without all that other data. I am limited in my excel knowledge and don’t know how to create what he needs. I’ve researched, but while I see formulas that could help, I just don’t understand how to use them. Would you please help? I would really appreciate it. Thank you!
@Yesica, I would recommend contacting a consultant at ExcelRescue.net to get help customizing your spreadsheet.
Hi!
I would like to capitalize the first three characters in a string of letters using excel. The characters will be different for each line. Is there a set of functions I can use?
@TB. UPPER() will change text to uppercase. If A1 contained the text you wanted to change, you could use =UPPER(LEFT(A1,3))&RIGHT(A1,LEN(A1)-3)
I have a column of data with an 10 digit date (xx-xx-xxxx) in the beginning of each data item. I would like to remove the date field and leave the rest of the data. The length of each data item is variable, but the longest is 60 characters.
@Fred, To remove the first 10 characters using a formula, use =RIGHT(A1,LEN(A1)-10) assuming your data is in column A.
I have a log where I would like the name column to automatically fill the adjoining column to the client’s location. I think this is a simple task, as I have seen it in other logs but I have no clue what function it is.
@Patty, The instructions on the Add a Customer List to Your Spreadsheet page describe how to set up a list and use lookup functions to return customer info based on the chosen customer. Hope that helps.
I have a spread sheet, column shows.
Last name, middle initial, first name or
Last name, first name
I need it to convert to, first name last name (no middle initial if there)
I’ve tried many formulas and can’t get it right.
Example:
Report pulls:
Smith A John
Smith Jane
Need:
John Smith
Jane Smith
@Ash, You could try Flash Fill (Ctrl+e) to create columns for Last Name and First Name. If “Smith A John” is in cell A1, type “Smith” in cell B1 then press Ctrl+e. Then enter “John” in cell C1 and press Ctrl+e. Then enter =C1&” “&B1 into cell D1 and copy the formula down (or double-click on the fill handle). If Flash Fill doesn’t work for you, let me know and we’ll come up with a formula that works.
I have an Excel spreadsheet and I have several Columns that include reviewer comments, formatted as follows:
|Reviewer: *Review*| Reviewer: [No comments]| Reviewer :*Review*|
i have this in multiple rows
My question is, is there a way to filter and remove the Reviewers that have “[No Comments]” ?
@DulanJay … Yes, using the Data > Filter tool, that should be pretty simple. [You should save a backup copy of your file before doing this.] First use the Filter tool to hide all the rows you do NOT want to delete. Then select the group of rows containing the rows you want to delete and press F5 or Ctrl+g to open the Go To window. Click on the Special button. Then select “Visible cells only”. Then right-click on one of the selected rows and select Delete Rows. Then clear your filter.
I have a string of numbers that I need to have individually listed, one number per cell. Right now that string is being identified by have the smallest number in one cell and the largest in another. Instead of displaying like this I need to have the entire string of numbers to display. What is a formula that I can use to do this? Thanks in advance!
@Michelle, can you provide an example? You mean you have something like 123456 and you need it to be split into 6 cells? Is it an actual number, or is it a text string? See tip #16 if the number is or can be converted to a text string first.
John, thank you for your reply and my apologies for taking so long to respond! I actually already received an answer as I had put this request on several message boards so that I could ensure getting my project completed! But now that I know that you monitor this sight frequently I will save this to my favorite Excel message boards :D Thanks again!!!
I have a column of several locations with names that I would like to change. For eg. FTL should be BROWARD, ATLANTA should be FULTON, how can I do this?
You could try using Find and Replace (CTRL+H).
Cell Example:
John Smith Capital Management, Chicago
How can I change Chicago to be smaller font and italics and then reflect that format to all cells in the column? I know how to get the smaller font & italics in that cell, but I don’t know how to copy that format to the others.
@Katie, It would be cool if Flash Fill could do that, but unless you are splitting the city out to a separate cell, I don’t think that’s going to work. When you copy formatting, Excel isn’t smart enough to apply italics and changes in font size and color within a portion of text to the correct places in other strings of text. Maybe someday Flash Fill will get to be that smart.
Hello, my data currently exists as-
Cell A1: Smith Family Denti
Cell B1: stry 123 Main Street
But it should be-
Cell A1: Smith Family Dentistry
Cell B1: 123 Main Street
Looking for a formula to cut the “stry” at the start of cell B1 and move to another column that I can concatenate with cell A1 and also clean up the address in cell B1. I have 12000 rows of customer data. Some rows have this issue, some don’t. Some only have 1 character to cut out, some have more.
Any help is much appreciated! I’ve spent a whole day Googling and trying different formulas that haven’t worked.
@Brenda, The key in data cleanup is to identify patterns that you can detect and apply to the list as a whole. For example, if all street address should start with a number, then maybe you could use a formula in a helper column (or a conditional format rule) to identify all the addresses that start with a non-number. Something like =ISNUMBER(VALUE(LEFT(address,1))) might work. The VALUE function converts numbers into values for the ISNUMBER test. After that, you could filter based on the helper column or the formatting.
Maybe a secondary helper column returns the location of the first space in the address via loc=FIND(” “,address). Then, with the combination of the two helper functions (meaning that you only do the next operations on the rows that really need it), you could use =LEFT(address,loc-1) to grab the text from the start of the address and concatenate it onto the name. And use =MID(address,loc+1,5000) to strip the address.
Good luck!
Thank you Jon! I’ll try it out and post an update
How Can I get the answer on column B by using Excel Formula
@Moidul. The answer to what? What do you want in Column C? If you mean how do you look up the project code based on the invoice number, that’s a VLOOKUP thing. =VLOOKUP(invoice_no,A:B,2,0)
Thank you very much for your prompt reply, but if you please ignore the Project Code Column i.e Column B ( I have mention or the reference only) then by what formula I will pick up the project code from Column A only since in Column A the project code text string in different Nth Position. Can you help by any formula by selecting Column A only
I see. You might have to take this to a data expert consultant. I can’t see any patterns in your data that would allow you to pull out the invoice number. If there is a pattern, then you could come up with a formula. Is there any way you can identify the part of the string that comes before and after the invoice number? If not, I don’t know how you’ll be able to pull the invoice number out of these seemingly random strings.
I see a pattern in your list. And I think solution on my analysis be like this,
18JH42A330EN00710076 =IFS(LEN(C486)=20,MID(C486,7,4),LEN(C486)=14,MID(C486,6,4),LEN(C486)=16,MID(C486,3,4))
18JH42A330EN00720077 =IFS(LEN(C487)=20,MID(C487,7,4),LEN(C487)=14,MID(C487,6,4),LEN(C487)=16,MID(C487,3,4))
18JH44A330RT00720086 =IFS(LEN(C488)=20,MID(C488,7,4),LEN(C488)=14,MID(C488,6,4),LEN(C488)=16,MID(C488,3,4))
18CH2S011S0047 =IFS(LEN(C489)=20,MID(C489,7,4),LEN(C489)=14,MID(C489,6,4),LEN(C489)=16,MID(C489,3,4))
18JK2T007G0111 =IFS(LEN(C490)=20,MID(C490,7,4),LEN(C490)=14,MID(C490,6,4),LEN(C490)=16,MID(C490,3,4))
18TN2O004G0280 =IFS(LEN(C491)=20,MID(C491,7,4),LEN(C491)=14,MID(C491,6,4),LEN(C491)=16,MID(C491,3,4))
18PM080154WB0648 =IFS(LEN(C492)=20,MID(C492,7,4),LEN(C492)=14,MID(C492,6,4),LEN(C492)=16,MID(C492,3,4))
18P055N0365WB117 =IFS(LEN(C493)=20,MID(C493,7,4),LEN(C493)=14,MID(C493,6,4),LEN(C493)=16,MID(C493,3,4))
Thanks a lot for your prompt reply. Its my pleasure you reply my problem. Thanks once again
Hi! I’ve just been charged with acquiring and organizing all inner office communications for my team. Much of the data is collected from outlook and is simply copied from the message (date, subject, from, and body text) and copied into a excel worksheet. Not the most elegant solution, but we sometimes need to look back at past decisions long after the email retention policy has expired, and the emails have been long deleted.
The problem is the date that I COPY from the message header (Outlook 2016), which is formatted with a 3 character day of the week, the month, day, and four digit year, and the time, as follows:
Mon 11/1/2020 00:45, Wed 1/15/2020 00:30, or Fri 5/5/2020 00:15.
Because the date is pasted into the cell it does not want to follow the formatting rules. I want to use that date as a file name, but in a different order (2020-05-05_0015) and with all special characters removed. The order is important to ensure it will always sort accurately (unless, of course, there is a better method). I know I can use replace, substitute, len, right, left, & and other text manipulation methods, but I’m getting stuck on how to call those functions when the length of the month and day is inconsistent.
What is the best method for splitting this date up so I can CAT it back together? How can I ensure 2 digit month and day (with leading zero)? If I were typing this I could rely on the cell number format, but because the data is pasted, the values do not want to follow those date formats. By the way, macros are not an option in this corporate setting.
Currently this is a completely manual process, so any help is appreciated.
Bill Collins
Hi Bill, I think the easiest way to work with that data would be to use B1=VALUE(RIGHT(A1,LEN(A1)-4)) to remove the first 4 characters and convert it into a datetime value. Then, you could use C1=TEXT(B1,”yyyy-mm-dd\_hhmm”) to convert the datetime value into the format that you want.
Thanks! I knew there had to be a way to do it, and I was hoping I was right. I have not yet tested this, but it makes pefect sense to me–just that I never thought of that! Thank you!!
Here’s a question for you.
I have a column containing information as text. In that column are state codes as well as other information. It’s sort of a catch all data field. In each case, the state code is preceded by a carrot (shift+6, “^”). I would like to create a new column that has a list of the states.
For example, Column B2 contains “drove a honda through ^CA ^NV ^UT ^WY”
I want C2 to read “CA, NV, UT, WY”
The number of states varies, so one line may include 4 states, the next may only have more or less.
I’m sure this involves searching for the “^” symbol and creating an array of the two characters following the “^”, then using textjoin=(“, “, TRUE, [aray]). I’m not sure how to make the array.
Assuming that you have one state per row, then yes, after pulling the states into a separate column, you could use TEXTJOIN. To extract the state, you could use the MID function and FIND function like this: =MID(A1,FIND(“^”,A1)+1,2)
Here’s a challenge (for me for sure): In a short sequence of letters (this is DNA), I want to search the last three letters and change them to Cs. Two examples: if the sequence is ggcacaagctggagtacaact then return ggcacaagctggagtacaatt. If the sequence is ggcacaagctggagtacaccc then return ggcacaagctggagtacattt. Thank you !!!
Think of this in two parts: (a) how do I get the last 3 letters and then (b) how do I change the Cs to Ts (c) how do I return the first part of the text so that I can append the modified text to the end. For (a), you can use =RIGHT(text,3). For (b) you can use the SUBSTITUTE function. For (c) use LEFT and LEN. If the text was in cell A1: =LEFT(A1,LEN(A1)-3)&SUBSTITUTE(RIGHT(A1,3),”c”,”t”)
Wow Jon I really appreciate your reply. When I run this I get the #NAME? error. I dismantled the equation to try to pinpoint the problem: =LEFT(A1,LEN(A1)-3) works, =RIGHT(A1,3) works, =LEFT(L21,LEN(L21)-3)&RIGHT(L21,3) works, but when I add SUBSTITUTE it doesn’t work. Can you suggest a fix?
I wanted to also say that entering just =SUBSTITUTE(RIGHT(A1,3),”c”,”t”) produces the same error.
First, check if your version of Excel has the SUBSTITUTE function. If it does, make sure you are using correct double quotes around c and t, not special characters that just look like double quotes (like the ones you are using in your follow-up response).
Jon, That was a brilliant observation, you were right, it was the font format for the quote characters. I would have never found that. Anyway, all is well now. We are making nucleic acid-based drugs for ALS and now you’re a contributor. Many Thanks!!
am trying to replace the sign ##### in a template for 491 variables.
“#####” means that the column is too narrow to display the contents. Widen the column.
Hi there,
I am trying to extract text strings from one column to another, thing is if there are duplicates in the first column I only want it to show up in the second column once. Any thoughts?
Is there formula to choose the first 12 digits(letters) in each cell, of a column and change that font color only, leaving the rest black?
Not that I know of. I don’t know of any formulas that will change the color of just a portion of text in a cell. Check out the related suggestion on Excel.Uservoice.com
I have been trying and failing to come up with a solution to an audit I have to perform. I have about 20,000 rows of data for different companies. I need to identify duplicate addresses for any and all businesses. There are potential spelling errors/typos, as well as different formatting for things such as street, st, st. and so on. There is also an issue where the street number is sometimes indicated numerically “1” or written out “One”. The closest I have come to creating an efficient audit is creating a unique identifier with a concat of the FEIN with the zip code and then manually auditing. Do you have any suggestions on how to improve this process?
@Jamie, I can’t think of a simple solution right now, though I know that the power query tools can be very powerful. Excel people who specialize in data analysis could probably answer this pretty well. You might try asking this on a general Excel forum. You might also look for an address validation and correction tool. This USPS page might be a place to start looking.
How do I create a function that returns the raw text instead of the formula? I want a formula that performs the paste value operation.
@Mark … sounds like you want a macro instead of a formula.
I Have cells with strings that when the user inputs a value in a certain box I want the string to update. ex; 2 X 9ga X ? wire. Where the question mark would need to be updated by a different cell
Hi, how to use native excel commands to re-arange all
C234, 423C into 234C (in ascending order
AABA, BAAA, ABAA into AAAB ( in ascending order)
Thank you !
@Bluesky: Convert the text into an array of characters, then use SORT and then CONCAT: =CONCAT(SORT(MID(A1,ROW(INDIRECT(“1:”&LEN(A1))),1)))
Hi,
I am working with a car database where there’s a column that contains different sorts of car information in a seemingly random way. Recently I have complied a list of chassis codes that may be present there. In another worksheet, I have also created a table of car model names against those chassis codes for future VLOOKUP uses. Now, how to find cells that contain my chassis codes? And is it possible to bring the relevant model names in the next column?
@Muyid, Not enough detail to provide a quick specific answer. I’d recommend asking your question via an Excel forum, or perhaps get a quote from an Excel consultant. When contacting an Excel consultant, providing an example set of data would definitely help.