Find and Replace Excel Function

Find and replace found in Microsoft applications is a powerful function that we can use to manipulate data in any Microsoft applications like word, excel, power point etc. This tool can be used to find and replace any kind of data including blank spaces, account strings without any data, mis-spelt words, etc. In this article, I will focus on how to use this function in a Microsoft excel worksheet.

How to use Find and Replace Excel Function

Sometimes we receive accounting data from financial information systems applications but this data is not in a format that we need for further analysis. Below is an example:

1 A B C
2  Account Number   Description   Amount 
3 01-20001  Bank one   $       11,000
4 01-20002  Bank two   $         3,456
5 01-20003  Bank three   $       98,765
6 01-20004  Bank Three   $24,355,677
7 01-20005  Bank four   $       23,455
8 01-20006  Bank five   $     122,345
9 01-20007  Petty Cash   $         8,765
10 01-20008  Bank Seven- Payroll   $     997,876
11 01-20009  Bank Eight-Operating   $       12,345
12 01-20010  Bank Nine- Checking   $23,445,656
13 01-20011  Investments   $       98,765

You notice that the account number has a space between the numbers. Now you need to eliminate these spaces. But you are faced with a challenge because the only way you know is to go to the data and manually eliminate the spaces. However, the data is more than one hundred pages long. So this option is not the best because the approach will take you several hours and even if you do it, it is not full proof as it is bound to many errors.

Microsoft Excel has a function that can sort out this issue pretty fast. Below are the steps that you will need to take to solve problem expeditiously:

  1. In our example data, ensure that the data of our interest, that is, the account number is formatted as text
  2. Highlight the data by selecting cells A:3 to A:13
  3. If you are using Microsoft Excel 2007 version then go to Editing>Find and Select>Replace
  4. A Find and Replace window opens
  5. In the ‘find what’ box type  the ‘space’ that is  –     This is the item we need the excel function to look for in our selected data and then replace it (delete it)
  6. Then click ‘replace all’ tab. This function then replaces (deletes) all the un needed information. See the results below, easy eh!
1 A B C
2  Account Number   Description   Amount 
3 120001  Bank one   $       11,000
4 120002  Bank two   $         3,456
5 120003  Bank three   $       98,765
6 120004  Bank Three   $24,355,677
7 120005  Bank four   $       23,455
8 120006  Bank five   $     122,345
9 120007  Petty Cash   $         8,765
10 120008  Bank Seven- Payroll   $     997,876
11 120009  Bank Eight-Operating   $       12,345
12 120010  Bank Nine- Checking   $23,445,656
13 120011  Investments   $       98,765
This entry was posted in Microsoft Excel Simplified. Bookmark the permalink.

22 Responses to Find and Replace Excel Function

  1. I think this is one of the most significant info for me. And i am glad reading your article. But should remark on some general things, The site style is ideal, the articles is really nice : D. Good job, cheers

  2. I am not new to blogging and really value your blog. There is much prime subject that peaks my interest. I am going to bookmark your blog and keep checking you out. Wish you good luck.

  3. Randy Chaney says:

    Very interesting points you have mentioned , thankyou for posting . “Whatever one man is capable of conceiving, other men will be able to achieve.” by Jules Verne.

  4. I think you have observed some very interesting points , appreciate it for the post.

  5. Wow, amazing blog layout! How long have you been blogging for? you made blogging look easy. The overall look of your site is great, let alone the content!

  6. Absolutely pent subject material, Really enjoyed reading through.

  7. Normally I do not read post on blogs, however I would like to say that this write-up very pressured me to check out and do it! Your writing taste has been amazed me. Thank you, very great post.

  8. Simply wanna remark on few general things, The website style is perfect, the written content is real excellent : D.

  9. ea says:

    I really enjoy looking at on this site, it holds good blog posts. “Beauty in things exist in the mind which contemplates them.” by David Hume.

  10. Thanks for sharing superb informations. Your website is very cool. I am impressed by the details that you’ve on this web site. It reveals how nicely you understand this subject. Bookmarked this web page, will come back for extra articles. You, my pal, ROCK! I found just the info I already searched everywhere and just couldn’t come across. What a great site.

  11. Utterly composed subject material, Really enjoyed looking at.

  12. Outstanding post, I think people should learn a lot from this web blog its very user pleasant. So much excellent info on here :D.

  13. I was looking at some of your posts on this website and I conceive this internet site is very instructive! Continue posting .

  14. I went over this website and I believe you have a lot of wonderful info, saved to my bookmarks (:.

  15. Aw, this was a really nice post. In thought I want to put in writing like this additionally ?taking time and precise effort to make a very good article?however what can I say?I procrastinate alot and on no account appear to get one thing done.

  16. Aw, this was a very nice post. In thought I would like to put in writing like this moreover ?taking time and precise effort to make a very good article?but what can I say?I procrastinate alot and by no means seem to get one thing done.

  17. Kip Eduardo says:

    Would you be thinking about exchanging links?

  18. Youre so cool! I dont suppose Ive read anything like this before. So good to find any person with some authentic ideas on this subject. realy thank you for starting this up. this website is something that’s needed on the internet, somebody with a bit of originality. helpful job for bringing one thing new to the web!

  19. Thanks so much for writing all of the excellent information! Looking forward to checking out more posts!

  20. Hello! I could have sworn. I’ve been to this blog site before but after browsing through a number of the post I understood it’s new to me. Anyways, I’m absolutely glad I stumbled onto it and I’ll be bookmarking and checking back regularly!

  21. You have remarked very interesting details! ps decent internet site.

  22. Appreciating the commitment you put into your website and in depth information you present. It’s great to come across a blog every once in a while that isn’t the same old rehashed information. Excellent read! I’ve bookmarked your site and I’m including your RSS feeds to my Google account.

Leave a Reply

Your email address will not be published. Required fields are marked *