There come times in Excel where you need specific Data out of a String Value. This is the case while I am working with Custom Format, specifically Scientific Formats. As I don’t work with Scientific formats in my day to day life I had to go read up how this actually works. And yes I think the way Excel calculates it is great.
The thing is I want to show the difference between doing the calculation Manually and how Excel would do this. For this, I need the rightmost section of the string it creates. Example the Scientific of writing 123456789 would then be 1.23E+08. Thus I need to Extract 08 from the right to do some Calculations. Similar I need the leftmost section which is 1.23.
To get the examples to show correctly I had to do some Manual typing. If you followed along with the post about Custom Formats you will know that Excel does not actually change the data. It merely displays it in a professional way that is easily readable for the person working on the Spreadsheet. With this being said if you manually type 1.23E+08 into a cell, excel picks up it is a Scientific value and actually understands the Cell Value as 123000000.
To get the Data to actually display the way I needed it for the examples I am going to use I had to set the Cell to Text. So if you see the green triangle in the corners of the cells this is intentional.
P.S. For you that picked up that Value difference in Scientific for 1.23E+08, kudos to you for picking it up. This is the reason I want to show the difference between Excel’s way and manually doing the calculations.
The syntax for both functions is basically the same with the only difference being the one counts from the left and the other from the right.
This Text is Required. The text string containing the characters you want to extract from.
This is Optional. Specifies the number of characters you want LEFT\RIGHT to extract
To get this right with the example 1.23E+08 we actually need to help with two other functions. Firstly find which returns the numeric value of a character in a string, in this case, the position of the plus sign. Next Len which returns the length of a cell. If you simply take the Length Minus the Position of the plus sign you will have the number of characters to return on the right side of the string.
On the other hand, extracting the left of a string is much simpler. We still have to find the position of the Capital Letter “E“. But then the formula is as simple as to Minus one from the Position to get the number of characters you require to return.
Viola, there we go. Extracted both the left and right of scientific notation value. So before we can get back to Custom Formats for Scientific we need to still understand the Power Function.