Excel Text Functions
Though logical functions like IF, SUMIF, AND, OR are more commonly used along with the many numerical functions in Excel there are some really useful TEXT based functions. “Text based functions in Excel? They exist?” You might be saying, Yes they do! These text based functions are often less known about as most of the time functions or formulas that are used in Excel are numbers based and so most forget about the text based ones, or forget to use them! We cover some text based functions in our Advanced Excel training course.
Here’s a selection a few text based functions in Excel with their definitions.
MID Function
This function returns a string of characters from the middle of a string. Note: this does not mean it returns the exact middle! It returns a string of characters from anywhere within the string
E.g. =MID (a1,4,2) a1 contains a part number AB/4TX/3K would return 4T as 4 is the starting position and 2 the length
CONCATENATE Function
This function joins together a combination of cells and strings.
E.g. =CONCATENATE(“MR. “,a1,” “,b1) where a1 contains John and b1 contains Legge would return ‘MR. John Legge’
SEARCH Function
This function searches for a character in a string and returns the first position of the first character in that string
E.g. =Search(“ “,a1) if a1 contains John Legge the answer would be 5 the position of the space
LEN Function
This function returns the length (characters) of the string of text including any spaces
E.g. =LEN(a1) if a1 contains John Legge, the answer would be 10
Try them out and let us know how you get on. These Excel text functions along with many others are covered in one or two of our many excel courses we offer, so you can learn more about them by coming on one of our courses.
Please contact us for more details through the contact page on our website here.
If you liked this hint and tip, why not take a look at our previous one on some Excel basic hint and tips?