Find or Replace Text and Numbers in Excel (surprising features)
HTML-код
- Опубликовано: 24 июл 2024
- Join 400,000+ professionals in our courses here 👉 link.xelplus.com/yt-d-all-cou...
Use Excel's Find and Replace feature to not only find and select all cells, but to also find and replace cell formatting.
You can use Excel's shortcut keys Ctrl + F to get to the FIND dialogue box and Control + H to get to the replace dialog box.
⬇️ Download the free practice workbook here: pages.xelplus.com/find-replac...
🔍 What You'll Discover:
- Accessing Find & Replace: Learn the quickest ways to launch Find & Replace in Excel, including time-saving keyboard shortcuts.
- Exploring Hidden Options: Dive deep into the lesser-known features of Find & Replace, such as searching within comments, formulas, and even specific formatting.
- Advanced Search Techniques: Understand how to tailor your searches for precise results, whether you're looking within a single sheet or an entire workbook.
- Replacing with Formatting: Learn how to not just replace text but also apply new formatting styles in one go, saving you valuable time and effort.
- Smart Formatting Replacement: Discover how to efficiently update cell formatting across your workbook - ideal for company-wide branding updates.
You can search for a specific text in formulas, cells and also comments. You have the option to search inside the entire workbook or a specific worksheet.
You can get more advanced with Excel's find and replace feature by searching for a specific cell formatting and replacing it with another. For example, you need to replace one cell color with another color. You can quickly do that with Find and Replace.
⯆ Blog post: www.xelplus.com/excel-find-an...
LINKS to related videos - Excel basics playlist: • Excel Introduction - L...
★ My Online Excel Courses ► courses.xelplus.com
➡️ Join this channel to get access to perks: / @leilagharani
👕☕ Get the Official XelPlus MERCH: xelplus.creator-spring.com/
🎓 Not sure which of my Excel courses fits best for you? Take the quiz: www.xelplus.com/course-quiz/
🎥 RESOURCES I recommend: www.xelplus.com/resources/
🚩Let’s connect on social:
Instagram: / lgharani
LinkedIn: / xelplus
Note: This description contains affiliate links, which means at no additional cost to you, we will receive a small commission if you make a purchase using the links. This helps support the channel and allows us to continue to make videos like this. Thank you for your support!
#excel
![Cordae - Saturday Mornings (feat. Lil Wayne) [Official Music Video]](http://i.ytimg.com/vi/z2GYJua09F0/mqdefault.jpg)
Download the file I used in the video from here 👉 pages.xelplus.com/find-replace-excel
Awesome!. I used Find & replace but never knew that we can find for a format/color & replace them. Great aspect. You're a really awesome mentor to the subscribers
Leila, no matter how much I think I know about a certain Excel feature or function, you always manage to uncover all these hidden gems when I watch your videos. Thanks once again for another great video!
I'm glad you find these tutorials helpful Robert. Thank you for your support!
Thanks Leila. You always find something new and interesting in Excel we all “should” know, but don’t. I for one have seen the format button there, but have not used it. I’d be the one searching manually for all the blue to change to green. Thanks for the video.
Awesome Leila!!! I can't imagine so much hidden in excel in find and replace. You are making me to learn and new dimensions to look it in every video you post. Keep up the good work.
You are right again... I have been 'staring' at Excel for years and have never used these basic features before! Thank you Leila.
Glad you found something useful Robert!
It proves that we don't see many things which are in front of our eyes, as such do not even explore them. That's where a role of a teacher starts... To show a correct path. Thanks a lot Leila
Hi Leila. After "find all", to select all, I believe it ought to be Ctrl + Shift + down arrow key (not just Ctrl + down arrow key). When I did Ctrl + Shift + down arrow key, I was able to select all results and then format them as you told us how. Excellent video. Thank you very much and Cheers!
I never actually 'bothered' to consider Find/Replace as anything else but just that... text replacement. But as with all your tutorials you always shed new light on things, you rock! :)
I have been struggling with finding good resources for my excel questions until I came across your videos. Thanks Leila for putting through such an amazing job.
I'm very happy to hear that! Thank you for your support.
Awww... enlightening. replace colors and being able to mass highlight the finds.... awwww LOVE IT, upset I didn’t know about it years ago. Thank you Leila
Oh great, not done before. Not only this, Your every tutorial is awesome. You are really genius dear.. Keep it up
Indeed Find and replace option helps us a lot. I used them very frequently as it is too helpful when you need to change anything in your workbook. You can change your formula reference, you can filter bold part, you can change whole formula ( I used this trick to put SUBTOTAL instead of SUM in multiple cells)
Really great option in excel. Thank you Leila sharing more things like highlighting all the found value in one time.
Thank you Ankur for your feedback. Agree - it's very helpful for adjusting formulas too :)
@@LeilaGharani Except "Replace" no longer works in 365 *_except_* on "formulas". I used to use it all the time but they've stuffed it up and its so, so annoying!
I have been into data science for close to 10 years now and I must say that you are like a greek goddess for excel users both beginners and expert users!! Great going!!👍🏻
Greek goddess ... I like that :)
You always teach something new about what we are already using but in a very limited and conservative way. You are superb! Thank you so much
Thank you Rahul. I'm very glad to hear these tutorials help.
Thanks Leila Very good refresher. Use this function regularly to change text with formulas across multiple sheets and multiple tabs, which helps to expedite the process immensely. I learned from your video about selecting from within the found cells box how to highlight and applyi formatting. Helpful as well was the color picker, remembering the RGB values and applying them replace or find sections. Thanks
You're welcome Antonio. Agree the color picker can come in handy.
U go to quick..v
The ability to replace the format was really helpful (and unknown till now) Thank you very much
This blew my mind!!!! You are a genius
You are the best I have seen on RUclips on this subject. Thank you!
Hi Leila.. thanks for the great video on Find and Replace which I use often for anything from modifying text and formatting to cleaning data to prepare for use in a Table. Another goodie on the VBA side that I learned from Jon Acampora of Excel Campus is using the Range.Find() method for identifying the last used row and column in a worksheet, without having to pick a specific row or column to search on. This can be useful if the height or width in your table or work area may contain blanks or is not necessarily uniform and contiguous and you want more precision than provided by the .UsedRange method which can give inaccurate results if a larger range has previously been used in the worksheet than is currently being used. Jon's approach is as follows:
lRow = Cells.Find("*", Range("A1"), xlFormulas, xlPart, xlByRows, xlPrevious, False).Row
lCol = Cells.Find("*", Range("A1"), xlFormulas, xlPart, xlByColumns, xlPrevious, False).Column
It is a bit more to type than the Range.End(xlUp) or Range.End(xlToLeft) method, but it is bulletproof in terms of always identifying the last used row or column without first having to identify which row or column to test. Using it in VBA made me even more aware of its usefulness directly in Excel at the dialog box level and vice versa. Your video shows some of the many ways to put it to good use directly from within Excel. The possibilities are really endless. Thanks for the insights and thumbs up!
Thank you for sharing the VBA tip from Jon - that's a great method to get more precision. Thank you Wayne!
This is awesome ....Thanks for getting in deep into a simple feature like Find and replace ..awesome Tips
Good vid. I use it a lot with CTRL J to replace Alt Enter spaces within a cell.
Wow... I've been reformatting so many spreadsheets one by one 😂 You are a game changer. Thanks!!!
Thanks so much Leila.
I came in here to find a specific find and replace option.
You showed me how and then I also learned more that I didn't even know excel could do.
Really going to cut my time in half 🙂
Wonderful! Glad it was useful.
Really at start I thought it would be worst but after watching full video came to know the better use of function. Thanks
Great advice about find and replace which has a lot more uses than I thought!
One of those simple things that I never knew. Very useful - Thank you 👍
You were right. Picked up some very useful tips. Especially like the ability to replace colours so easily. Thank you as always for your knowledge shares.
You're very welcome. Glad you found something new here.
Thanks to my Beautiful/Brilliant/Brainy/Witty Excel Teacher, for teaching us in an Excellent way. In every Video you are making us to learn & learn something, and become smarter. Thanks a lot.
Used for finding text and number, never format. Awesome! You have just added a new tools to my toolbox. Thanks
Same here.
Wow. You're absolutely right. Will definitely use this in the future. Thanks so much. More power to you!
I like the find and replace functions, especially with the options and the find all and replace all possibilities. And I am very happy you use a lot of keyboard shortcuts !
It's just faster with shortcuts, right?
Mind blown!!... i had no idea that you can find and replace formatting :O Amazing tip! :)
You wouldn't believe the timing. I am just about to make formatting changes to a large workbook over several sheets and this will smae me a whole lot of time. Many thanks.
That's perfect :)
All your videos are super and informative...keep it up...I want you to note that I use Find and replace in several ways like adding $ or string over a group of data sets to make it constant..another way is convert the formula into text by replacing = with " =" this helps in modifying the formula properly.
Glad you like the videos. These are good applications of find and replace. Thanks for sharing!
I’m going to save so much time! THANK YOU - (perfect addition to DRY: Don’t Repeat Yourself)
This is useful in VBA also, e.g. building "en masse" formulas whose reference(s) must be determined later. Set a placeholder in the meantime and apply find/replace before completion.
Been using my favorite math program Excel for years, and still learned something new here. Thank you. One question... is there a quick way to reverse the find and replace? Not undo, but to "replace" perhaps the last replace in an "all replace".
I always learn something from you.
Thank you Leila! Exactly what I needed to replace 2021 with 2022 within my formulas in my cells.
👍
I'm learning and it's quite interesting how you explain what you're doing, using various hypothetical and actual, examples thank you Miss.
Cool, thanks Ted!
TIL that I can select the cells in the Find All list. What an awesome tip! Thank you for providing such great information!
Glad it was helpful, Simon!
Never used before. Knew it was there, but assumed only for text. Wow! Great tip! Thanks, Leila!
You're welcome Jim - I also hadn't realized it for the longest time....until we had to adjust the color scheme of all our workbooks at my previous company.
Thank you! I had forgotten the format aspect of this functionality.
Love this! Used the function before but only to replace characters not format. Tqvm for this video!
Glad you like the video.
Fantastic, you are a wealth of knowledge. I know Excel well, but you give me much more, thanks you.
I'm glad to hear that.
I use Search and Replace in Excel to force a carriage return within cells because I often work with non-numeric data in cells. So, for example, if I have three values in a cell with a space between them I search on space and replace with ctl+j (which is "forced" carriage return in a cell). That puts each of the tree values on its own line within a cell. I probably do this at least once a week in my work. Of course, delimiter can be anything-- not just space. Note that when you enter ctl+j as rep[lacement value nothing shows up in the window because it is "invisible", but it works in the resulting cell.
It's amazing. Your presentation is awesome. If you can provide a video on getpivotdata function, it will be your great help.
I use a lot find and replace.. Never thought to use for formatting purposes! That was cool!
I was waiting to see some formula replacements like copying a formula from an other workbook and then delete some references... (I hope this makes sense)
Thanks Leila. What a revelation! Keep up the great work.
Using find and replace function, but never used by this methods. It's new for me. Thank you mam.
You're very welcome Piyush.
Honestly I have never noticed to this find and replace,
Thank you for your guide
If you only want to search or find and replace in a select number of cells, rows etc you can by highlighting them before clicking go. really handy on find and replace to remove spaces, colons and the rest from sets of data
Yep, that's a good one. Thanks for the tip!
the way you explain the things are very easy to understand....such an great video.....
Glad you think so!
Thank you for the tutorial, I learnt some useful features of Find and Replace that I wasn't aware of before 🙏
Glad it was useful!
Omg, I didn't know you could do control+down in the search box!
I always learn something new and handy here. Thanks Leila!
Thank you Yulin! I'm glad you find something new in the tutorials :)
Nice video, I learned something, thanks. One thing you could add would be searching for special characters. I get lots of data from other applications, and I often have to replace the hidden carriage returns (like those created with an Alt-enter). This one can be found by typing CTRL-j in the search box, and it’s been very helpful for me. There are probably enough other special characters like that, that you could do a video on it.
Thanks for the info!
Thanks so much Leila. I will certainly learn so much from you.
Excellent feature very good for updating changing company preferences
Thank you for another great video
Glad you like the video Peter. It really can be very useful and often overlooked.
Ooooohhhh. I like the option of format replacements. That is awesome. Thank you!
Super helpful, as always!!! HUGE THANKS Leila - from China!!!
You're so welcome!
Your videos are very easy to follow. Do you have links to download the worksheet for the videos so I can practice while I follow along?
Thanks for great video. I used some of the features. It was a good reminder lesson
This is really interesting, have been using Excel for so many years yet never thought of this.
Nice video I used this method in daily work. Thank you.
Nice, thanks! I would like Google sheets to have this built in. Excel however could have built-in search and replace using regular expressions that Google sheets has.
Great videos. No need to memorize the RGB code in the end though. Click "Format" after selecting a format from a cell and you can edit the selected format.
Wow I hesitated on clicking on this link. I am glad I did I had no idea that you could select the found cells the search results.
Thanks David - I'm glad you found something new :)
I haven't used the format find before ! It's really looks useful, thanks.
Glad you like it!
As always, you are our source of inspiration.
Didn't know about the format functionality. Pretty handy. Thank you !
You're very welcome Herve!
I never knew about this on formatting. This is awesome!
Glad you like it.
Thank you leila. I'm always excited to view your next video. Thank you for amazing my job..
Excellent! As usual comprehensive view on selected topic. Uncheck "Clear find format" and suddenly "Find + Replace" starts to work again
Glad you like the video.
Very useful I am using frequently the RGB matrix for the custom Logos!!
Very cool ... I knew only a few, but you brought some amazing ones. Thanks for these tricks.
You're very welcome Luciano :)
You Helped me more than you can imagine it. Thank you
Great job, Leila. I learned something - as always when watching your videos
I'm glad to hear that Hubert!
Nice and thanks. I have never used it this way before. Thanks for the trick. Excel is really BROAD and you get bits and pieces of skills each day
Hi Shadrack - Agree Excel is so broad, I'm also learning something new all the time.
Thank you Leila...never used format replacing.
You're very welcome Zahid :)
Hi Leila, nice video as usual. Just wanna to share one trick I used to select all "Found" items... click any of the found items under the "Find result" and then press Ctrl+A. That's the magic key I really like. :)
Hope you like it too.
You're right! Control + A! Thanks :)
@@LeilaGharani My pleasure! :)
Blowed my mind with this. Thanks🙏❤️
Another important thing to note - you can restrict the Find/Replace to the cells you have selected prior to opening the dialog box. So, even though it appears to be set to work on the Sheet, it will work only on the previously selected cells.
For example, if you want to replace specific text in a single column while leaving untouched matches outside that column, select that column before pressing Control+H
That's a great tip! Thanks Duke.
@@LeilaGharaniHay leila.Could you please explain how to find even and odd numbers and replace them with letter E AND O?
each of ur video contain so much of knowledge......Thanx for great job that u r doing for us........ Keep it up Leila..
Thank you Jasvir. I'm glad you find them useful.
Cool..... never knew about replacing the format, thanks :)
Thank you a lot Leila, awesome as usual
I use find and replace, but not as detailed as what you have shown. Thanks again Leila!
You're very welcome Dante.
Never realise able to highlights from Ctrl+find option. Another tricks learn from your video.. Love it Leila, u r excel angel.. ;)
I'm glad you found something new here kenny :)
I like your depth and focus .. thank u 📈
EXCELLENT-WELL DONE.PARTICULARLY THE COLOR FORMATTING.
Thank you! Cheers!
To use find All, Instead of selecting with mouse you can also use Ctrl +A to select all. After selecting you can do whatever you want with found items and format the selected cells as you want. It will do the things in one go
love the Control + A. Thanks Prabhu.
You're Welcome Leila!
Always coming with New Miracle, once again thanks a lot for sharing valuable information.
Thank you for your support Anil! I'm glad you like the tutorials.
Really simple and easy to understand, great 👍
Thank you, it is very informative.
Just love your vids!!! Thank you so much!🥰
Great explanation for search and replace in excel! Did you already find and replace your necklace?
This one was very good . I was looking to see how to change the color of the search box, but from what I see on different sites Excel may not allow this modification. Great video though thank you.
Your voice is so soothing...
This was excellent! I learn so much from you. Thank you so much! 👍🏻💖👏🏻🌹
I'm very glad to hear that Rose! Thank you for your support.