Excel: use if to find & change text in column [Solved/Closed]

Report
Posts
1
Registration date
Monday February 8, 2010
Status
Member
Last seen
February 9, 2010
-
Posts
1862
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
July 30, 2015
-
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

Posts
1862
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
July 30, 2015
797
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
Thank you

A few words of thanks would be greatly appreciated. Add comment

CCM 2942 users have said thank you to us this month

Subscribe To Our Newsletter!

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!