Now that I’ve discussed the text functions len() and find(), it’s time to get back to how to convert numerical text to numbers.
Example 1: Stripping characters off the ends (e.g. $ and %).
If you have a piece of text that’s number with a known number of non-numerical characters, you can use len() to remove them. I touched on this when I was first discussing the length function. The most common examples of this are currency and percentages.
For example, if you have “9%”, “99%”, or “99.3% in A1 and you wanted just the numbers, you could use =left(A1,len(A1)-1). The len(A1) tells you how long the contents of A1 are, and so len(A1)-1 is one short of the full contents. Therefore left(len(a1)-1) is all but the last character.
Likewise, you could extract $5, $50, and $50.25 with =right(A1,len(A1)-1).
Example 2: Performing mathematical operations (e.g. fractions and scientific notation).
There are a few number formats which are actually equations, although we don’t think of them that way. For example, we think of 1/4 as “one fourth”, but it can also be read as “one divided by four”. Likewise, 6E7 is actually “six times 10 to the power of 7).
This is where find() comes in handy. Here’s how:
A1 (original text) | Extraction formula |
---|---|
99/100 | =left(A1,find(“/”,A1)-1)/right(A1,len(A1)-find(“/”,A1)) |
Breakdown: | fraction top: find(“/”,A1)=3 3-1=2 left(A1,2)=”99″ fraction bottom: |
3.1415 E 6 | =left(A1,find(“E”,A1)-1)*10^right(A1,len(A1)-find(“E”,A1)) |
Breakdown: | first part: find(“E”,A1)=8 8-1=7 left(A1,7)=”3.1415 “ last part: middle part: |
1 21/25 | =left(A1,find” “,A1)-1)+mid(A1,find(” “,A1)+1,find(“/”,A1)-(find(” “,A1)+1))/right(A1,len(A1)-find(“/”,A1)) |
Breakdown: | whole number: find(” “,A1)=2 2-1=1 left(A1,1)=”1″ fraction top: fraction bottom: combined: “1”+”21″/”25″=1.84 |
Example 3: Extracting dates.
Dates are a little more complicated. 2/3/2013 can’t be extracted using the same formula as 12/31/2012, unless you’re using find() to determine where the “/” are. However, there’s more than one “/” per date. Therefore you have to use the second form of find(), the one that uses a starting character, to find the second “/”. Essentially, to find the second “/” you’d need to start looking at one character after the first “/”.
That can get a bit complicated, and very difficult. So to make things easier, when you’re starting out I’d recommend having 3 formulas instead of one. Let column A be named “TextDate”, column B be named “FirstSlash”, column C be named “SecondSlash”, and columns D through F named “Month”, “Day”, and “Year” respectively.
Then the formulas would be as follows.
FirstSlash | =find(“/”,TextDate) |
Find the location of “/” in TextDate. In 11/9/13, FirstSlash would be 3. | |
SecondSlash | =find(“/”,TextDate,FirstSlash+1) |
Find the location of “/” in TextDate, starting at the character after FirstSlash. If you started it at FirstSlash, instead of the character after it, then “/” would be the first character it saw. In 11/9/13, SecondSlash would be 5 | |
Month | =left(TextDate,FirstSlash-1) |
Get everything to the left of, but not including, FirstSlash. In 11/9/13 this would be the first two characters, or “11”. | |
Day | =mid(TextDate,FirstSlash+1,SecondSlash-(FirstSlash+1)) |
From the middle of TextDate, starting at the character after FirstSlash, get a number of characters equal to the difference between the location of SecondSlash and where you’re starting. For example, in 11/9/13, FirstSlash=3 and SecondSlash=5. So you the formula would parse to mid(TextDate,(3+1),5-(3+1), or mid(TextDate,4,1), and so you would get 1 character starting at character 4, i.e. “9”. | |
Year | =right(TextDate,len(TextDate)-SecondSlash) |
Get all of the characters, except those at SecondSlash or before. In 11/9/13 this would parse to right(TextDate,7-5), so you’d just get the two characters at the end, which would be “13”. |
That’s it for today! In a week or two I’ll discuss a variety of date functions that will let you combine your extracted values into an actual date.