Using FIND() to separate text strings.

In this example we are going to use the Excel function FIND() combined with LEFT() to separate some text. If you aren’t familiar with LEFT(), RIGHT() and MID() you should visit our tutorial on this first before diving into this one. You can find this by clicking here or using the Blog search. A common task is peoples full name (both first name and last name) being populated in one single cell. You may want to separate these in to single cells, a column for first name, a column for last name. So, let’s take our example below. As you can see we have 4 names of varying length in characters. In order for Excel to know where to split the names we must first find out where the space “ “ is between the first and last name. For example the name Joe Bloggs, the space is character number 4. To do this we use the FIND() function. There are 3 arguments in...
Read More

Using =TRIM() in Excel.

The Excel function =TRIM() removes extra spaces from the beginning, middle or end of text strings. It also removes unwanted line breaks leaving just a single space between words. This is especially useful when data is exported from other applications and the output has extra line breaks and/or spacing. The example below illustrates the formula. The syntax for =TRIM() is: =TRIM(text) The argument (text) is the text from which to remove the extra space. There are many other built in functions in Excel for cleaning up , or extracting specific parts of a text string. Check out our tutorial on extracting text strings using ‘=LEFT()’, ‘=RIGHT()’ and ‘=MID()’ by clicking here. ...
Read More

Using the INDIRECT() formula to refer to a cell reference from a text value

Every once in a while I come up against a challenge where I have to refer to data from multiple sources, but they're not already handily linked.  BUT I may have a handy list of names or references in some kind of text format.  (Note, the formula is exactly the same in Microsoft Excel as it is in Google Sheets; I have used Excel for these screenshots). For example, I may have a workbook with multiple tabs formatted the same e.g.:  And a summary table ready to fill in:  We know that we could quite easily click in cell B2 on our summary sheet, type the equals key, then click on cell B2 on the Lancaster tab to fill in that cell.  And dragging the formula down would fill in the rest of the months.  But if we only did this, we'd need to repeat the process for the other four tabs.  No big deal for this example, but these kind of workbooks...
Read More

Extracting text strings in Excel using ‘=LEFT()’, ‘=RIGHT()’ and ‘=MID()’

Let’s take a well-known phrase: The cat sat on the mat. Below are 3 examples using ; ‘=LEFT()’, ‘=RIGHT()’ and ‘=MID()’ and the results. These formula’s can be extremely useful for things like separating first and last names in cells, or splitting postcodes or telephone numbers from area codes. Now let’s break each formula down to see how it works. LEFT(text,num_chars) Returns the specified number of characters from the start of a text string. So in our example text is cell B3, and the numbers of characters we returned is 7 (remember to include any spaces as characters.) The result is The cat   RIGHT(text,num_chars) Returns the specified number of characters from the end of a text string. So in our example text is cell B3, and the numbers of characters we returned is 4 (remember to include any special characters, in this case the full stop.) The result is mat.   MID(text,start_num,num_chars) Returns the character from the middle of a text string, given a starting position and length. So in...
Read More