Search

# Text Functions - Advance Excel

There are hundreds of formulas in Excel. In this post I would like to cover some of the Text Functions in Excel.

Assumption: A1 has a word Upskill Technologies

Len: Len returns the number of characters in a string.

Ex: =Len(A1) would return 20

Left: Left returns n number of characters from a string starting from left.

Ex: Left(A1,4) would return Upsk

Right: Right function returns 'n' number of characters from right.

Ex: Right(A1,4) would return gies

Mid: Mid function returns 'n' number of characters from a position specified.

Ex: Mid(A1,3,5) would return the characers skill

Find: Find functions returns the position of a character in a string.

Ex: Find("k",A1,1) would return 4

Ex: Find(" ",A1,1) would return 8

Now Combining all the above functions if you want to write a formula to get the 1st name that is to split cell A1 using space as delimter then we use something like below.

First Name: =LEFT(A1,FIND(" ",A1,1)-1) would return Upskill

The above formula find the position of space using find function which would return a value 8. And then Left formula gets the 7 characters(8 minus 1) from the left of cell A1.

Last Name: =RIGHT(A1,LEN(A1)-FIND(" ",A1,1)) will reutn Technologies

The above formula finds the characters from right after the position of space. Position of space is found using length of A1 i.e. 20 and position of space i.e.8 so remaining 12 characters from right.

Upper: Upper formula converts the text to upper case

Ex: Upper(A1) would return UPSKILL TECHNOLOGIES

Lower: Lower formula converts the text to lower case

Ex: Lower(A1) would return upskill technologies

Substitute: This substitutes replaces specific text in a string

Ex: SUBSTITUTE(A1," ","_",1) would return Upskill_Technologies

We learned in our classes the other practical uses of text functions and how to use them along with If conditions, vlookups,sumifs....

34 views

### Recent Posts

See All

#### Stop computer from locking windows and keep status Active on Skype / Teams / Jabber using VBA scrip

With the pandemic pushing almost everyone to work from home and most of us are required to stay active on collaboration tools even if there is no need for you to sit in front of laptop. Like a friend

#### TEST YOUR EXCEL SKILLS IN EXCEL

So you want to know how good you are in excel then make sure you download our test now. Its not a multiple choice question style test but an actual test in excel with excel data. Download it and try t

#### FREE SQL TRAINING FOR HYDERABAD STUDENTS

Hi All- There has been a request from most of the students attending excel and VBA classes to show what SQL queries are and how SQL can be used with Excel and VBA. SQL is a query language to retrieve,