These are the most important Excel Text Functions you *NEED* to know

[@rajanghadi2337]

You perfectly selected sir which I also feel the best Text functions. I also consider SUBSTITUTE alongwith

[@gregbernard7861]

Agree

[@chandoo_]

Thanks Rajan... I am glad this one hit the sweet spot.

[@aniesc3257]

I cant thank you enough! I decided to learn more about excel, even installed on my personal laptop, and watch your easy-to-follow tutorials (including sample files!). Thank you for your effort in teaching others, teaching me how to use excel!

[@shreyanshdangi4366]

Very nice explained video...thanks sir

[@denizaksen2972]

AWESOME as usual, just AWESOME from an AWESOME EXCEL Guru and Instructor for those humble minds who wish to become AWESOME in the world's MOST AWESOME business software. 13 years and still counting, since March 2008. Not only I, but generations of my students learnt so much from your AWESOME videos and posts, Chandoo. Namaste!

[@chandoo_]

Thank you sir. I am blessed and humbled to have your support and cheering for all these years.

[@shreyanshdangi4366]

Thanks for this very onformative video

[@clifforddsouza4855]

Great video Chandoo found a wonderful solution with your help as I work with this sort of data very often. Thanks so much.

[@lakbaylaboy2644]

Ur my favorite teacher Chandoo! Shoutout from uae

[@chandoo_]

Thanks Lakbay.... 😀

[@डकुमेन्टमेरा]

I like way you teach. 👍

[@chandoo_]

Glad to hear that

[@wayneedmondson1065]

Hi Chandoo. Awesome TEXT function examples! They can be put to use and so many creative ways. Thanks for sharing :)) Thumbs up!!

[@chandoo_]

Thanks Wayne... Comeback and share your inventions :)

[@hussainakhtar1094]

I have used this on my own data and love this .. thanks Chandoo

[@chandoo_]

Thank you Hussain... :)

[@Live-ro3rs]

Very informative video 🙏. Requesting A video on extracting First, Middle, and Last Name.

[@deepk82]

Incredible video editing!!! 👍👍👍👍👍 Excel is amazing as usual!

[@chandoo_]

Thanks again!

[@gvinodnair]

You're awesome Chandoo and letting your viewers also do the same thing. 👍

[@chandoo_]

Thank you Vinod... 😀

[@santoshpv321]

This is superb...I really loved the way you presented it...so easy to understand

[@chandoo_]

Thanks a lot 😊

[@logeshmuthu2971]

Expecting this video from you for a very long.Thanks

[@chandoo_]

Thank you. I am glad you liked this.

[@Go4Truth]

always appreciate and regards your efforts for that you are doing for Excel new and advance learner and your way of teaching is also very awesome. Thanks on behalf of many people whom like your work always

[@chandoo_]

You are most welcome

[@gladysditirro1422]

Chandoo, love all your videos and learned a lot from them. I would appreciate if you can go a little bit slower though to grasp the concepts better. Thank you very much for sharing your knowledge 😊

[@hemantvyas8893]

Great candoo bhai

[@nonoobott8602]

Really great tutorial and apt use-cases for each of the functions. Thanks for sharing

[@chandoo_]

Glad it was helpful!

[@canirmalchoudhary8173]

Chandoo hatsoff to you, tha last substitute formula with len and search I use for finding ghost space, but I couldn't figure out logic but after explanation now I got that too.

[@chandoo_]

Thank you Nirmal. Yes, the LEN (SUBSTITUTE) trick is a valuable one.

[@Manoj-lg5zc]

Thanks Chandoo. Also do a video to convert Date & Time which are in Text format.

[@chandoo_]

Sure... I will add a video on it. Meanwhile, check this page too - chandoo.org/wp/date-time-tips-ms-excel/

[@masternecrushne]

Thanks Chandoo. when I have amounts with currency as a text and I want to turn it into a number, I am using =left(text,len(text)-2) or -1, demands in the currency and spaces, and then text to column.

[@Shubhammv22]

Or you can use "Find & Replace" and replace your currency with blank

[@masternecrushne]

@@Shubhammv22 how can you find symbols?

[@Shubhammv22]

@@masternecrushne you yourself said that it's part of your text, you can simply copy the symbol from that text and use find and replace

[@masternecrushne]

@@Shubhammv22 thanks, I will try that 👍

[@chandoo_]

Thanks Or Guy... you can also try the NUMBERVALUE or VALUE functions in Excel. See this for some explanation - chandoo.org/wp/convert-european-number-format/

[@andrewbishop9467]

Excellent video as always

[@chandoo_]

Glad you enjoyed it

[@slashd]

0:14 Love the Find joke 😂

[@sree9355]

Sir, could u please tell what is the difference of using Text join and conconcate function

[@psn801]

Hello Chandoo , Can you tell me which version of excel you are using. Because i am not getting all function in excel 2016.

[@chandoo_]

I am using Excel 365. You should be able to use most of the functions in the video in any version of Excel.

[@mirrrvelll5164]

Smooth, very useful!

[@chandoo_]

Glad to hear that!

[@qamarfarooq5295]

Love you chandoo

[@chandoo_]

Aww... love you too 😍

[@yashikamakkar9858]

Awesome ❤

[@howtoexcel5900]

Thank you Sir, this is very useful

[@chandoo_]

You are welcome

[@balanagarajank5635]

Thanks chandoo, your videos are super useful. What software do you use to record and edit your videos ?

[@chandoo_]

Hi Bala... Thank you. I use Camtasia to record & produce most of my videos. You can download a free trial here - techsmith.z6rjha.net/chandoo

[@idrismustapha3095]

Hi super Chandoo, is there different between the text join and concatinate?

[@chandoo_]

Thanks Idris. TEXTJOIN can add an optional delimiter CONCAT() will simply combine text from a range of cells CONCATENATE will combine text from cells, but you must select each of the cells in the formula (no range option) & is same as CONCATENATE but shorter.

[@taizoondean689]

Thanks sir 👍

[@chandoo_]

Most welcome

[@shoaibrehman9988]

Gr8 Video Sir Thanks

[@chandoo_]

Most welcome

[@prakashbabu6173]

Great posting Chandoo garu👍 I need to know how to split the excessive text from one cell ..??

[@chandoo_]

Thanks Prakash :) What is the criteria for such excessive text? You can use LEFT, SUBSTITUTE or REPLACE to remove parts of text.

[@eddyonemolease4555]

Is there no formula that will help us search for characters instead of letters. It will save us ALL these complications

[@chandoo_]

Hi Eddy... well said. The Excel functions feel like they are stuck in early 90s. Just the other day when I demoed TRIM function, someone said "Why is it called TRIM, why not removespaces()?" I wish Microsoft would add more functions to make our life simple.

[@sudhalanka4299]

Hi Chandu..Very helpful Video.. Accidentally I watched one video of yours and ended up watching all. Btw are you from Hyderabad?

[@chandoo_]

Wow... thanks for that and welcome to my channel. I am originally from Vijayawad, but lived in Hyd and Vizag as well before moving to NZ.

[@raghvendrasingh9721]

Sir nyc one to introduce what you are teaching today... Amazing😂😂👏👏👏

[@chandoo_]

Thank you so much 😀 I had fun shooting that bit.

[@kailashkumartodi1273]

Excellent

[@chandoo_]

Thank you so much 😀

[@denizaksen2972]

What happens when a full name contains multiple first names (typical in Portugal and Spain, but in many other countries too)? Here is my long formula that would work in such cases along with its explanation: *=MID([@Name], SEARCH(CHAR(8), SUBSTITUTE([@Name], " ", CHAR(8), LEN([@Name]) - LEN(SUBSTITUTE([@Name]," ","")))) + 1, LEN([@Name]))*

[@denizaksen2972]

*Step 1:* Find the number of BLANK SPACES in the name by subtracting the length of [@Name] without blank spaces from the length of the original [@Name]. Let's call that number B for ease of tracking. *Step 2:* Substitute the B'th occurrence of the BLANK SPACE in [@Name] with some nonletter character, e.g. the TAB character which is given by *CHAR(8)* in Excel. *Step 3:* By using the *SEARCH()* function, locate now the position of CHAR(8) in the string obtained in *Step 2.* Progress from that position by one character so as to bypass the CHAR(8) character itself. Let's call the new position X for ease of tracking. *Step 4:* Using the *MID()* and *LEN()* functions, extract all the characters (which are guaranteed to be letters exclusively) of [@Name] from position X up to its end. *Remark:* I deliberately extract *LEN([@Name])* characters from position X onward to make sure my formula will work no matter how long the original string [@Name] is.

[@denizaksen2972]

Needless to say, unnecessary BLANK SPACES must have been wiped off the cells in the [@Name] column of the table with the old, good *TRIM()* function beforehand.

[@chandoo_]

That is a good solution. Thank you for sharing it :)

[@hussainakhtar1094]

very nice example. But, the sample file cannot be downloaded, it opens your video again

[@chandoo_]

Sorry about that. Please try again. I fixed the link now.

[@durgasouryarajpappu3432]

Hi chandoo We can use trim function to get rid of spaces instead of substitute function. Which one is better?

[@chandoo_]

TRIM will not remove all the spaces. It will only remove any spaces in the middle that are excessive. So =TRIM(" Cha n d o o ") becomes "Cha n d o o" You should use SUBSTITUTE to count words as demoed in the video.

[@gyaantank1493]

For last name ,I have used midfunction but it's showing - you have entered too few arguments

[@pgkannan]

Thanks

[@chandoo_]

You are welcome Kannan...

[@kalpeshpanchal7411]

I are amazing

[@FRANKWHITE1996]

Thanks