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
1864
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

1 reply

venkat1926
Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
808
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