For Professionals working frequently with text Microsoft Excel will probably not be the tool of choice; after all, it is not a text editor.
However when it comes to databases and manipulating text values in them, excel can prove to be very useful, once you find out which of the countless functions can help with your task.
Have you ever wanted to:
- Change the lower case into upper case or the other way round?
- Create a list of email addresses in one format from available names?
- Extract names from email addresses?
- Add text in a specific place?
Here is a choice of formulas and methods , which might help you with your tasks, I hope you will find them useful.
1. Change case
You can convert a text into UPPER of LOWER case, or capitalize only the first letter of every word into upper case using PROPER formula.
Capitalizing only the first letter of the first word in a string is a bit more complicated and requires using this , using this combination of formulas: =UPPER(LEFT(A2,1))&LOWER(RIGHT(A2,LEN(A2)-1))
2. Merge text
How to create e-mail addresses from the database of names
CONCATENATE(text1,text2,text3...) formula simply joins a number of strings into one. You can choose specific cells to be joined or text you want (using parenthesis).
This is great for example if you have a list of people's names, and want to create email addresses for them in the same format.
Here is how:
Notice that we also used LOWER formula, to keep the email address in lower case.
As an alternative, you can also use ampersand to join the text together so, to get the same result as in the example above, we would have: =LOWER(C2)&"."&LOWER(B2)&"@rtl.com"
3. Replace text
With Excel you can specify which part of text you want to replace with something else. There are actually many ways to do that.
choose your scope – where you want to find text to be replaced; you can also choose whole cell
specify what you want to amend
specify what you want to have there instead
if there are a few occurrences of old text in your chosen scope, you can specify which occurrence should be amended; if you don't put any number here, Excel will replace every occurrence of the old text it can find in your scope.
It is useful if you want to have certain text at certain position in a string, for example you want to change a few letters in the middle into asterisks.
So as arguments, we have old_text – which again can be the whole cell
start_num – a position of the first character to be replaced
num_characters – how many characters are we replacing, and
new_text – with what.
In this example the number of characters we wanted to replace varied in different cells; we knew where it starts and we knew that it is the last word we want to replace. So instead of giving a specific number we used a formula to calculate it for each cell: LEN(A2)-7, so the number of all characters in A2 cell minus the number of characters we wanted to keep (7).
We can also add a new text string starting at a specified position using this formula –by specifying the number of characters to replace as 0:
4. Extract text
How to get area codes from phone numbers or names from email addresses
LEFT, MID and RIGHT formulas allows to specify how many characters we want to extract from a string, respectively starting from the left side, from the middle at a certain position, or from the right side. First we need to choose the text (or whole cell), then give the actual number of characters. For The MID function this number is preceded by the number indicating the position to start from.
Now suppose we have a list of email addresses and want to extract usernames from them. Because of their different length we will also need to use FIND formula. It will return the position of “@”, and by substracting 1 from this number we will get the number of characters before “@”, that we actually want to extract from the text.
There is another very simple method of dividing a string within a cell– using "Text to Columns" function, in Data tab. In case of email addresses we chose “@” as delimiter.
When using a formula amend text, what you have in your target cell is still “only” a formula – you can't just click in the cell and start changing your text further. In order to do so, you can copy the cells, and Paste special – Values.
For the same reason, you can't simply search for text in your target column with formulas. You can either copy and Paste Special – Values, as above, or change your search criteria in Find & replace window for Values.
Thank you for reading!