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(...)

--
Bob Kilmer

"Andrea" wrote in message
m...
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!

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

"Andrea" wrote in message
m...
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!

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.

In article ,
"Bob Kilmer" wrote:

In VBA, Replace() is available since, I believe XL97.

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