Excel - Automatically change text in column

January 2017





Issue

I want to search the column A, with numbers as text as follows:
#305  
#777
#78
#82
#884


I need a formula that will search column A and change any two digit numbers to three digit with a leading zero.

I want the change to take place within the existing data in column a so that example data:

a1 lkjljlk blah #305  
a2 dkfjalfj blah #55


would end up as

a1 lkjljlk blah #305  
a2 dkfjalfj blah #055

Solution

Suppose A1 to A5 is
305  
777
78
82
884


In B1 type this formula:

=IF(LEN(A1)=2,"0"&A1,A1) 
  • copy B1 down
  • highlight B1 down
  • edit copy
  • highlight B1 only
  • edit -pastespecial-choose values and click ok
  • now delete column A


Do this as an experiment and you will get hang of it.

Note

Thanks to venkat1926 for this tip on the forum.

Related


Published by aakai1056. Latest update on October 20, 2016 at 09:36 AM by owilson.
This document, titled "Excel - Automatically change text in column," is available under the Creative Commons license. Any copy, reuse, or modification of the content should be sufficiently credited to CCM (ccm.net).