Lesson#22: How to find the exact middle character(s) of text using LEN
LEN is a function that is used for finding out the number of characters in a text string. Its syntax is LEN(text).
THE QUICK BROWN FOX JUMPS OVER THE LAZY DOG has 43 characters including <space> between words.
Now I will show an example of how to find the exact middle character(s) of a text string using LEN.
Logic will be if the text string has an odd number of characters then it will find out the middle one else it will find out the two middle characters of the string.
I have used the formula =IF(MOD(LEN(C4),2)=1, MID(C4, ((LEN(C4)-1)/2+1), 1), MID(C4, LEN(C4)/2, 2))
This text is having 43 characters so it has found out 22nd character in the string.
I have added an extra character at the end of the string and made the characters number 44 so it has found out the 22nd and 23rd characters in the string.
Related Video Tutorials:
Hi! I am Puspendu. I am the founder and author of Excelabcd. I am little creative person, blogger and Excel-maniac guy. I hope you enjoy my blog.
Leave a Reply