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

Question

Previous Topic Next Topic

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

Report

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

- 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

Excel: use if to find & change text in column
Excel date format won't change ✓ - Forum - Excel
Excel click on cell to change value of another cell - Forum - Excel
Excel/VBA - A macro to change the color of a row - How-To - Excel
Excel - Compare two lists and change row color - How-To - Excel
Excel - Run Macro on Cell Change - How-To - Excel

1 reply

Ask a question

venkat1926 · Accepted Answer · 2010-02-09T04:47:14-0500

Answer 1 / 1

venkat1926

Posts: 1862
Registration date: Sunday June 14, 2009
Status: Contributor
Last seen: July 30, 2015

797
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.

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

1 reply

Your reply

Subscribe To Our Newsletter!