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!

Here’s a simple list of a few text based functions in Excel with their definitions:

  • MID 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 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 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 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 web site www.jplcomputer.co.uk/contact

Cookie Control

Cookie control

We have placed cookies on your device to help make this website better.

I'm fine with this

We have placed cookies on your device to help make this website better.

You can use this tool to change your cookie settings. Otherwise, we’ll assume you’re OK to continue.

Some of the cookies we use are essential for the site to work.

We also use some non-essential cookies to collect information for making reports and to help us improve the site. The cookies collect information in an anonymous form.

To control third party cookies, you can also adjust your browser settings.

I'm fine with this
(One cookie will be set to store your preference)
(Ticking this sets a cookie to hide this popup if you then hit close. This will not store any personal information)
Information and Settings Cookie policy