take out the "Dots" ????

  • Thread starter Thread starter Angela
  • Start date Start date
A

Angela

Hello,

I have a cloumn, lets say A1, and its all numbered like this:


05.03.01.01
05.03.01.02
39.01.01.01
39.01.01.02
etc...


How can I remove all the dots?
because I need the zero in front of the 5, the column had to be
fromatted as "text"...

I tried the find and replace, FIND: **.**.**.** and
Replace with ********

unfortunately, it did work, but not as a "wildcard", it literally
replaced it with the *.


Is their a short VB macro code , or formula, that could remove the
dots from a range in coulmn A1, lets say from A1 to A2000? ( or any
number. )

Thanks so much, I am stumped, and have tried the Knowledge Base,
forums, etc. and a few hours on my own before I cried " HELP! "



Any help will be greatly needed and helpful.
Thanks
 
=substitute(a1,".","")

in a helper cell will leave it as text and remove the dots.

Any chance you could just do an edit|replace and change the dots to nothing and
give the range a custom format of:

00000000

They'll be numeric, but they'll look pretty.
 
Hi
I would advise strogly against using a Text format. It is often better
to Custom format the cells as "00000000" then use a slight amendment to
Franks formula

=SUBSTITUTE(A1,".","")+0

This will ensure the results are real numbers rather than Text. Now copy
these results and Edit>Paste special - values over the top of the
orginals.

***** Posted via: http://www.ozgrid.com
Excel Templates, Training & Add-ins.
Free Excel Forum http://www.ozgrid.com/forum *****
 
Back
Top