Now let’s consider a slightly more difficult situation. In the following example we have a description and an amount in the same cell, but the two are always separated by a hyphen:
Because neither the length of the text or the figure are necessarily consistent, we can’t use Left, Right or Mid. However, we can instead use the hyphen to work out where the description ends and the number begins. To do this we must first identify how many characters from the left there are before the hyphen.
To do this we use the ‘Search’ function. Here is the function screen for Search:
Note again that we can just type the hyphen into the ‘Find_text’ box and Excel will automatically add the speech marks. Note also that there is the option to specify the character position at which you want to start the search. This is useful if you need to locate more than one similar character – once you have found the first, you can start the next search from one character position higher. Our example is a simple one that doesn’t use the ‘Start_num’ argument and, as you can see, it returns the position of the hyphen as character 6.
=SEARCH(”-”,A13)
We can now ‘nest’ the Search function within the ‘Left’ function to retrieve the description:
=LEFT(A13,SEARCH(”-”,A13)-1)
In order to exclude the hyphen itself we have subtracted 1 from the result of search. If we copy this formula down our list we can see that it achieves the desired result:
Now to deal with the amount. Whilst we can use Search to find the starting position, we don’t yet know how long the amount is. We can work this out using the ‘Len’ function. ‘Len’ is a very simple function with just one argument – the text string, or cell containing the text string, that we wish to find the length of:
=LEN(A13)
This tells us how long the text is in total, and we have already used Search once to find the position of the hyphen. By combining Len and Search we can calculate how many characters follow the hyphen:
=LEN(A13)-(SEARCH(”-”,A13))
In the case of “Sales-10000” Len will return 11, the hyphen is at position 6, so 11-6 = 5, the number of characters in the amount.
We can use this with the ‘Right’ function to pick out the amount:
=RIGHT(A13,LEN(A13)-(SEARCH(”-”,A13)))
Again we can copy this formula down the list:
As you can see above, whilst we have indeed separated out the amount characters, Excel is still treating our text as text and if we used Sum to total column C we would get zero:
We need to convert the text ‘amounts’ into proper numbers. To do this we use the function Value. We will use the value function to convert the three items in our list to numbers. Here is the formula for cell D13:
=VALUE(C13)
We can now copy this down our list and use Sum again to total our new column:
As you can see, the text values are now treated as numbers and Sum works correctly.
These two functions can be used to remove unwanted characters from text. Sometimes, if you import text from other sources, you may end up with non-printing characters, such as carriage returns – Clean will remove these. Trim can be used to get rid of extraneous spaces:
In the following example we have part of an address that includes multiple spaces between ‘High’ and ‘Street’ and a carriage return character to separate the lines of the address.
In column B we have used Trim to get rid of the extra spaces:
=TRIM(A19)
and then in column C we have used Clean on the result to remove the carriage return:
=CLEAN(B19)
Note that the Trim function leaves a single space between High and Street, but that the Clean function removes the carriage return entirely.
No comments:
Post a Comment