Excel Tutorials - String Functions 2

https://youtu.be/d0XUQVdyaN4

Last week we went over several functions that help you read, write, and manipulate text in a cell. We'll go over a few more of those this week: Right, Mid, Len, and Trim.

The transcript of what I intended to say in the video:
Our example spreadsheet looks pretty similar to the one we used last week, but this time, we need to get the numbers from the right end of a cell. This works exactly like the left function, but takes the right side of the string... as you would hopefully expect.

We will, again, work from the result backwards. We want the Power Output to say 28. But we want it to update if the voltage or current is changed. So, lets say the 28 is really =14*2. To get the 14, we will say Right(A2,2) and to get the 2, we will say Right(A1,1), but how are we going to make this work for any number of digits?

We need to find the equal sign. We can do that using the Find function, but that is going to give us 9...which is not the number we need. What we really need is the length of the entire cell minus the location of the equal sign. We can do that by saying Len(A1)-Find("=",A1). And for the 14, we will do Len(A2)-Find("=",A2).

Now, we are going to get an error: there are still spaces in the answer, because there is a space after the equal sign. The easiest way to fix this is to subtract one from the formulas we just used, but the safer way to do it is to use a function called Trim. Trim will delete any spaces before and after text or numbers. So, it will also help us if someone accidentally puts a space after the number... or adds two spaces instead of one!
Let's make B7=Right(B4, 5) and we'll get "Watts"

Finally, what if someone has used units on these variables? What if you have Voltage = 2 volts and Current = 14 amps? We could use a combination of Left and Right, but Excel has a function for this already: Mid. To find the 2, we are going to say Mid(A1,find("=",A1)+1,find("volts",A1)-(find("=",A1)+1)) and then we will trim it by surrounding the whole thing in Trim( ). Likewise, we will do the same thing with the 14: Mid(A12,find("=",A2)+1,find("amps",A2)-(find("=",A2)+1))

And there you have it! Please subscribe, comment, and like this video. If possible, please like the video from within Youtube rather than Facebook.

The next video will be about how to avoid showing errors on a spreadsheet that has not been filled in yet. Don't miss this one, it is very interesting and not very well known.

Finally, sometime in the next couple of weeks, my wife and I will be welcoming our second baby into the family. So, if I miss a week, that is why. Otherwise, I still plan to do one video per week for the foreseeable future.