Excel - Automatically change text in column

March 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.
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).