hi

I have cells whose values are like:
K-TGF
PCR 90 Q
TGF 800 T

i want to check that if anywhere spaces " " or slashes "/"
or "-" comes i want to remove it so that fianlly my result
is:
KTGF
PCR90Q
TGF800T

what is the easist method?

thanks in advance

Use Replace to replace " " with "", "-" with "" and "/" with "".

Sub Macro4()
Cells.Replace What:=" ", Replacement:="", LookAt:=xlPart, SearchOrder:=
_
xlByRows, MatchCase:=False, SearchFormat:=False,
ReplaceFormat:=False
Cells.Replace What:="/", Replacement:="", LookAt:=xlPart, SearchOrder:=
_
xlByRows, MatchCase:=False, SearchFormat:=False,
ReplaceFormat:=False
Cells.Replace What:="-", Replacement:="", LookAt:=xlPart, SearchOrder:=
_
xlByRows, MatchCase:=False, SearchFormat:=False,
ReplaceFormat:=False
End Sub


wrote in message
...
hi

I have cells whose values are like:
K-TGF
PCR 90 Q
TGF 800 T

i want to check that if anywhere spaces " " or slashes "/"
or "-" comes i want to remove it so that fianlly my result
is:
KTGF
PCR90Q
TGF800T

what is the easist method?

thanks in advance

thanks fpr a quick response bob...
was wondering there must a function which can help do this
particualr this more easily.
like INstr.... I can use it to find "-" and then so the
lengthy manipulation of subtracting lengths and all....
but its not a refined way of doing...so was thinking there
must be a function for it..
thanks again..
-----Original Message-----
Use Replace to replace " " with "", "-" with "" and "/"
with "".

Sub Macro4()
Cells.Replace What:=" ", Replacement:="",
LookAt:=xlPart, SearchOrder:=
_
xlByRows, MatchCase:=False, SearchFormat:=False,
ReplaceFormat:=False
Cells.Replace What:="/", Replacement:="",
LookAt:=xlPart, SearchOrder:=
_
xlByRows, MatchCase:=False, SearchFormat:=False,
ReplaceFormat:=False
Cells.Replace What:="-", Replacement:="",
LookAt:=xlPart, SearchOrder:=
_
xlByRows, MatchCase:=False, SearchFormat:=False,
ReplaceFormat:=False
End Sub


wrote in message
...
hi

I have cells whose values are like:
K-TGF
PCR 90 Q
TGF 800 T

i want to check that if anywhere spaces " " or
slashes "/"
or "-" comes i want to remove it so that fianlly my
result
is:
KTGF
PCR90Q
TGF800T

what is the easist method?

thanks in advance


.

Well, what to use depends in part on how you want to do it. In VBA code over
and over, or once in a while, by hand? Do you mean a worksheet function or a
VBA function? The Replace scheme I recorded is pretty easy and thorough if
you want to clean up data either on an entire sheet or a specific range. VBA
has a Replace function: Replace("ab-cd-ef","-","") returns "abcdef", but you
have to have your VBA code find the cells with "-" and the others in them to
act on, if that is the way you want to go. There are worksheet functions
that could be used, too.

As for Instr, it is common to iterate over the lenght of a string, using Mid
to get a single character then using Instr to find out if it is either in or
not in a string of acceptable characters and include the character in or
exclude the character from a new string being built as output. This is a
filter approach. Sometimes it is suitable.Because it works character by
chararacter, it is not well suited to large numbers of comparisons.

Recommand Look up:

VBA: Replace(expression, find, replace[, start[, count[, compare]]])

Excel: Replace Method of the Range object (which I ilustrated previously)
or of the WorksheetFunction object

wrote in message
...

thanks fpr a quick response bob...
was wondering there must a function which can help do this
particualr this more easily.
like INstr.... I can use it to find "-" and then so the
lengthy manipulation of subtracting lengths and all....
but its not a refined way of doing...so was thinking there
must be a function for it..
thanks again..
-----Original Message-----
Use Replace to replace " " with "", "-" with "" and "/"
with "".

Sub Macro4()
Cells.Replace What:=" ", Replacement:="",
LookAt:=xlPart, SearchOrder:=
_
xlByRows, MatchCase:=False, SearchFormat:=False,
ReplaceFormat:=False
Cells.Replace What:="/", Replacement:="",
LookAt:=xlPart, SearchOrder:=
_
xlByRows, MatchCase:=False, SearchFormat:=False,
ReplaceFormat:=False
Cells.Replace What:="-", Replacement:="",
LookAt:=xlPart, SearchOrder:=
_
xlByRows, MatchCase:=False, SearchFormat:=False,
ReplaceFormat:=False
End Sub


wrote in message
...
hi

I have cells whose values are like:
K-TGF
PCR 90 Q
TGF 800 T

i want to check that if anywhere spaces " " or
slashes "/"
or "-" comes i want to remove it so that fianlly my
result
is:
KTGF
PCR90Q
TGF800T

what is the easist method?

thanks in advance


.

here is a code that i found on net search...

which may be usefule to others also...
Here's the code:
Public Function RemoveSpaces(strInput As String)
' Removes all spaces from a string of text
Test:
If InStr(strInput, " ") = 0 Then
RemoveSpaces = strInput
Else
strInput = Left(strInput, InStr(strInput, " ") - 1) _
& Right(strInput, Len(strInput) - InStr
(strInput, " "))
GoTo Test
End If
End Function
How does it work?
This function accepts a single argument, strInput, the
string of text from which any spaces have to

be removed. At the core of the function is the InStr()
function. The InStr() function takes two

arguments: the first being the string of text to be
examined, and the character to be located. It

then returns the position of the first occurrence of that
character.

If I entered InStr("Martin Green","t") the result would be
4, the first letter "t" being the 4th

character in the string.

If I entered InStr("Martin Green"," ") the result would be
7, the first "space" being the 7th

character in the string.

The first line of the function is a label Test: marking
the start of the procedure. This is a

reference point for the function to return to so that it
can run again if necessary.
Next comes an If Statement which uses the InStr() function
to test the string to see if there are

any spaces. If the function returns a zero, then the
string does not contain any spaces and the

function finishes, leaving strInput as it was.
If the InStr function returns anything other than zero, it
means that a space has been found. So

the Else part of the If Statement removes it with the aid
of the text functions Len(), Left() and

Right().
The Len() function returns the number of characters in a
string (including any spaces), so

Len("Martin Green") returns 12.

The Left() function returns a given number of characters
from the left side of a string, so

Left("Martin Green",6) returns Martin.

The Right() function returns a given number of characters
from the right side of a string, so

Right("Martin Green",5) returns Green.

Armed with these functions I can figure out how long is
the strInput string. The Instr() function

tells me where the first space is, so I can use the other
text functions to take the characters

that are before the space, and the characters that are
after the space, and concatenate them (a

fancy word for joining them together) omitting the space.
But there might be more than one space in the string. This
method removes only the first space. So

the next line: GoTo Test sends the procedure back to the
beginning, where is checks for spaces. If

there are none the procedure finishes, otherwise the
process repeats until all the spaces are

removed.

-----Original Message-----
Well, what to use depends in part on how you want to do
it. In VBA code over
and over, or once in a while, by hand? Do you mean a
worksheet function or a
VBA function? The Replace scheme I recorded is pretty
easy and thorough if
you want to clean up data either on an entire sheet or a
specific range. VBA
has a Replace function: Replace("ab-cd-ef","-","")
returns "abcdef", but you
have to have your VBA code find the cells with "-" and
the others in them to
act on, if that is the way you want to go. There are
worksheet functions
that could be used, too.

As for Instr, it is common to iterate over the lenght of
a string, using Mid
to get a single character then using Instr to find out if
it is either in or
not in a string of acceptable characters and include the
character in or
exclude the character from a new string being built as
output. This is a
filter approach. Sometimes it is suitable.Because it
works character by
chararacter, it is not well suited to large numbers of
comparisons.

Recommand Look up:

VBA: Replace(expression, find, replace[, start[, count[,
compare]]])

Excel: Replace Method of the Range object (which I
ilustrated previously)
or of the WorksheetFunction object

wrote in message
...

thanks fpr a quick response bob...
was wondering there must a function which can help do
this
particualr this more easily.
like INstr.... I can use it to find "-" and then so the
lengthy manipulation of subtracting lengths and all....
but its not a refined way of doing...so was thinking
there
must be a function for it..
thanks again..
-----Original Message-----
Use Replace to replace " " with "", "-" with "" and "/"
with "".

Sub Macro4()
Cells.Replace What:=" ", Replacement:="",
LookAt:=xlPart, SearchOrder:=
_
xlByRows, MatchCase:=False,
SearchFormat:=False,
ReplaceFormat:=False
Cells.Replace What:="/", Replacement:="",
LookAt:=xlPart, SearchOrder:=
_
xlByRows, MatchCase:=False,
SearchFormat:=False,
ReplaceFormat:=False
Cells.Replace What:="-", Replacement:="",
LookAt:=xlPart, SearchOrder:=
_
xlByRows, MatchCase:=False,
SearchFormat:=False,
ReplaceFormat:=False
End Sub


wrote in message
...
hi

I have cells whose values are like:
K-TGF
PCR 90 Q
TGF 800 T

i want to check that if anywhere spaces " " or
slashes "/"
or "-" comes i want to remove it so that fianlly my
result
is:
KTGF
PCR90Q
TGF800T

what is the easist method?

thanks in advance


.



.

Most would use Bob's solution.

either the replace method to handle the entire worksheet or a range in the
worksheet

or replace to handle a string (rather than reinvent the wheel, like your
Removespaces code) or application.Substitute in earlier versions of excel.
Not sure why you prefer the long way around the block.

--
Regards,
Tom Ogilvy

wrote in message
...
here is a code that i found on net search...

which may be usefule to others also...
Here's the code:
Public Function RemoveSpaces(strInput As String)
' Removes all spaces from a string of text
Test:
If InStr(strInput, " ") = 0 Then
RemoveSpaces = strInput
Else
strInput = Left(strInput, InStr(strInput, " ") - 1) _
& Right(strInput, Len(strInput) - InStr
(strInput, " "))
GoTo Test
End If
End Function
How does it work?
This function accepts a single argument, strInput, the
string of text from which any spaces have to

be removed. At the core of the function is the InStr()
function. The InStr() function takes two

arguments: the first being the string of text to be
examined, and the character to be located. It

then returns the position of the first occurrence of that
character.

If I entered InStr("Martin Green","t") the result would be
4, the first letter "t" being the 4th

character in the string.

If I entered InStr("Martin Green"," ") the result would be
7, the first "space" being the 7th

character in the string.

The first line of the function is a label Test: marking
the start of the procedure. This is a

reference point for the function to return to so that it
can run again if necessary.
Next comes an If Statement which uses the InStr() function
to test the string to see if there are

any spaces. If the function returns a zero, then the
string does not contain any spaces and the

function finishes, leaving strInput as it was.
If the InStr function returns anything other than zero, it
means that a space has been found. So

the Else part of the If Statement removes it with the aid
of the text functions Len(), Left() and

Right().
The Len() function returns the number of characters in a
string (including any spaces), so

Len("Martin Green") returns 12.

The Left() function returns a given number of characters
from the left side of a string, so

Left("Martin Green",6) returns Martin.

The Right() function returns a given number of characters
from the right side of a string, so

Right("Martin Green",5) returns Green.

Armed with these functions I can figure out how long is
the strInput string. The Instr() function

tells me where the first space is, so I can use the other
text functions to take the characters

that are before the space, and the characters that are
after the space, and concatenate them (a

fancy word for joining them together) omitting the space.
But there might be more than one space in the string. This
method removes only the first space. So

the next line: GoTo Test sends the procedure back to the
beginning, where is checks for spaces. If

there are none the procedure finishes, otherwise the
process repeats until all the spaces are

removed.

-----Original Message-----
Well, what to use depends in part on how you want to do
it. In VBA code over
and over, or once in a while, by hand? Do you mean a
worksheet function or a
VBA function? The Replace scheme I recorded is pretty
easy and thorough if
you want to clean up data either on an entire sheet or a
specific range. VBA
has a Replace function: Replace("ab-cd-ef","-","")
returns "abcdef", but you
have to have your VBA code find the cells with "-" and
the others in them to
act on, if that is the way you want to go. There are
worksheet functions
that could be used, too.

As for Instr, it is common to iterate over the lenght of
a string, using Mid
to get a single character then using Instr to find out if
it is either in or
not in a string of acceptable characters and include the
character in or
exclude the character from a new string being built as
output. This is a
filter approach. Sometimes it is suitable.Because it
works character by
chararacter, it is not well suited to large numbers of
comparisons.

Recommand Look up:

VBA: Replace(expression, find, replace[, start[, count[,
compare]]])

Excel: Replace Method of the Range object (which I
ilustrated previously)
or of the WorksheetFunction object

wrote in message
...

thanks fpr a quick response bob...
was wondering there must a function which can help do
this
particualr this more easily.
like INstr.... I can use it to find "-" and then so the
lengthy manipulation of subtracting lengths and all....
but its not a refined way of doing...so was thinking
there
must be a function for it..
thanks again..
-----Original Message-----
Use Replace to replace " " with "", "-" with "" and "/"
with "".

Sub Macro4()
Cells.Replace What:=" ", Replacement:="",
LookAt:=xlPart, SearchOrder:=
_
xlByRows, MatchCase:=False,
SearchFormat:=False,
ReplaceFormat:=False
Cells.Replace What:="/", Replacement:="",
LookAt:=xlPart, SearchOrder:=
_
xlByRows, MatchCase:=False,
SearchFormat:=False,
ReplaceFormat:=False
Cells.Replace What:="-", Replacement:="",
LookAt:=xlPart, SearchOrder:=
_
xlByRows, MatchCase:=False,
SearchFormat:=False,
ReplaceFormat:=False
End Sub


wrote in message
...
hi

I have cells whose values are like:
K-TGF
PCR 90 Q
TGF 800 T

i want to check that if anywhere spaces " " or
slashes "/"
or "-" comes i want to remove it so that fianlly my
result
is:
KTGF
PCR90Q
TGF800T

what is the easist method?

thanks in advance


.



.