Excel: use if to find & change text in column

Solved/Closed
dobeekioskea Posts 1 Registration date Monday February 8, 2010 Status Member Last seen February 9, 2010 - Feb 9, 2010 at 01:50 AM
venkat1926 Posts 1863 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 - Feb 9, 2010 at 04:47 AM
Hello,

i want to search 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

thanks and thanks to everyone for all this great information on this site

vista
excel 2007
firefox
Related:

1 response

venkat1926 Posts 1863 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 811
Feb 9, 2010 at 04:47 AM
suppose a1 to a5 is
305
777
78
82
884

in B1 type thisformula
=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 experimetn and you will get hang of i t.
1