FORMULAS
Use LEN function to provide data for RIGHT function
If text strings you have in your range of data are not quite standardized and you need to modify them with one same formula, now you will learn how to use LEN function to provide data for RIGHT function. LEN function could be also used with other functions, such as LEFT and MID function for example, depending on your needs in particular moment.
Our today’s topic is very connected with our previous post Combine functions to insert a character inside a text string, and we hope you had the chance to read it. This post would be something like part two where we will use LEN function to handle variations in the number of characters of the string.
LEN function counts the number of characters of a text string, or to say differently, measures the length of a text string.
This property will be very useful for us when we have different number of characters in the string we want to edit. In the example from part one of this post series, we used RIGHT function to cut last four characters of the string “AB0001” and then we added it in our CONCATENATE function. Our final goal was to insert blank character after second character in the string and to create AB 0001 from AB0001. So, as the final result we created further formula:
=CONCATENATE(LEFT(A2;2);” “;RIGHT(A2;4))
If these four characters are not always present, because sometimes we have three characters, sometimes two etc. we can not just give a number 4 to RIGHT function, as we did previously, but we should forward to it the remaining number of characters after first two letter characters. If we have, for example, string like “AB12”, then we should just forward two characters to our RIGHT function, also if the string is “AB123” then we should forward three remaining characters.
In order to do it we will insert LEN function inside of our RIGHT function and decrease its result for 2 which will provide it needed number of characters after measuring the length of the string. New formula will look like this:
=CONCATENATE(LEFT(A2;2);” “;RIGHT(A2;LEN(A2)-2))
Meaning, if we have a string “AB123” there are 5 characters in total. We already used first two as letter characters, so we should provide number 3 to our RIGHT function and this we will get by using LEN decreased for 2 characters.
According to our new formula we can get results like on the picture below.
If you just missed details related to first part of formula, here you can read our part one post on the topic how to to insert a character inside a text string.
