removing unwanted characters in a cell

  • Thread starter Thread starter Andrea
  • Start date Start date
A

Andrea

Is there anyway to take the value in a cell and remove any spaces or
dashes "-"?

I'm needing to find data in it's simplest form and when operators
enter values, they are often inserting inadvertant spaces or in some
cases, dashes. Is there a macro or even an excel formula that will
remove any spaces or dashes?

Any suggestions would be greatly appreciated! Thanks!
 
In VBA, Replace() is available since, I believe XL97.

MsgBox Replace(Replace("a bc def-gh--i", " ", ""), "-", "")


Also see Help about the Replace method of the WorksheetFunction or Range
objects.

Application.WorksheetFunction.Replace(...)
Range("A1").Replace(...)
 
Highlight the cells in question; then at the toolbar, Edit, Replace
Round 1: Enter in replace what "-" without the quote marks
In next box - with what? Leave blank - enter nothing
Select replace all
with same area highlighted
Round2" Enter in the replace what Enter a single space (using your
space bar)
In the next box - with what? Leave Blank - enter
nothing
Select replace all

Re-format every thing
Back up your files before trying this..............

HTH
 
Replace() was introduced with VBA6 in XL00. For XL97 and MacXL
versions, use

With Application
.Substitute(.Substitute("a bc def-gh--i", " ", ""), "-", "")
End With

instead.
 
Back
Top