FIND & REPLACE ISSUE

  • Thread starter Thread starter William
  • Start date Start date
W

William

I have a colomn with 2-digits (currently text format):

03
04
10
20

.. . . etc.

I'd like to do a find and replace which adds three zeroes to the end, so
that I get this:

03000
04000
10000
20000

Is it possible?

Thanks
 
Not with find and replace.. but with a helper column...

So, if your column is in A, then in B, format as text, then type =A2&"000"
and paste down.
 
in the next column, can you just create a formula to add three zeros at the
end of the cell? For example, if your 2 digit text column starts in "A1", in
cell "B1" you can put the formula =A1&"000" and copy that formula down?
 
Can you change them to number format?

If yes...
Put 1000 in an empty cell.
edit|copy that cell
select your range to fix
Edit|paste special|multiply
clear the helper cell with 1000 in it.
Format the fixed range to show leading 0's:
Select the range
format|cells|number tab|custom category
00000
(or as many as you need)
 
try this
Sub addzerostotextcells()
For Each c In Selection
If Len(c) < 5 Then c.Value _
= c & Application.Rept("0", 5 - Len(c))
Next
End Sub
 
Thanks to all!

Dave Peterson said:
Can you change them to number format?

If yes...
Put 1000 in an empty cell.
edit|copy that cell
select your range to fix
Edit|paste special|multiply
clear the helper cell with 1000 in it.
Format the fixed range to show leading 0's:
Select the range
format|cells|number tab|custom category
00000
(or as many as you need)
Click to expand...
 
I do not see how you can do it with find and replace
A different way would be with a help column as follows...............
assuming your first number is in A1 insert a new column (B)
enter =A1&"000" in B1 and extend this down the column
then do the <edit><copy><edit<>paste special<>values trick to change the
formula's in B column back to values.
 
Back
Top