Can I find and replace "white space" in a cell in Excel?
 

I am using Excel 2002. I have a spreadsheet set up as a retrival matricie
that I will upload into an AS400. In one of the columns there are dimensions
that are set up as whole number space fraction; ie 3 1/2. I need to replace
the space with a "-" so that the cell would read 3-1/2. Is there a way to do
that using "Find and Replace". One of the spreadsheets has over 7000 rows, I
don't want to have to do this by hand.

Best to do it with VBA

Selection.Replace What:=Char(160), Replacement:="-", _
Look:=clipart, SearchOrder:=xlByRows, MatchCase:=False

--
HTH

Bob Phillips

"biggyb75" wrote in message
...
I am using Excel 2002. I have a spreadsheet set up as a retrival matricie
that I will upload into an AS400. In one of the columns there are
dimensions
that are set up as whole number space fraction; ie 3 1/2. I need to
replace
the space with a "-" so that the cell would read 3-1/2. Is there a way to
do
that using "Find and Replace". One of the spreadsheets has over 7000
rows, I
don't want to have to do this by hand.

Everything is already set up in Excel though. Is VBA visual basic?

"Bob Phillips" wrote:

Best to do it with VBA

Selection.Replace What:=Char(160), Replacement:="-", _
Look:=clipart, SearchOrder:=xlByRows, MatchCase:=False

--
HTH

Bob Phillips

"biggyb75" wrote in message
...
I am using Excel 2002. I have a spreadsheet set up as a retrival matricie
that I will upload into an AS400. In one of the columns there are
dimensions
that are set up as whole number space fraction; ie 3 1/2. I need to
replace
the space with a "-" so that the cell would read 3-1/2. Is there a way to
do
that using "Find and Replace". One of the spreadsheets has over 7000
rows, I
don't want to have to do this by hand.

I am able to find by the white space but when it replaces with the - it
changes the format. It will not keep it as a text format.

"biggyb75" wrote:

Everything is already set up in Excel though. Is VBA visual basic?

"Bob Phillips" wrote:

Best to do it with VBA

Selection.Replace What:=Char(160), Replacement:="-", _
Look:=clipart, SearchOrder:=xlByRows, MatchCase:=False

--
HTH

Bob Phillips

"biggyb75" wrote in message
...
I am using Excel 2002. I have a spreadsheet set up as a retrival matricie
that I will upload into an AS400. In one of the columns there are
dimensions
that are set up as whole number space fraction; ie 3 1/2. I need to
replace
the space with a "-" so that the cell would read 3-1/2. Is there a way to
do
that using "Find and Replace". One of the spreadsheets has over 7000
rows, I
don't want to have to do this by hand.

Clipart? <g

Char() is an XL function, not VBA

Chr(160) is a non-breaking space. For a regular space character, use
Chr(32)

Perhaps

Selection.Replace _
What:=Chr(32), _
Replacement:="-", _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
MatchCase:=False

This assumes that the numbers are Text. If the numbers are simply
formatted as 0 ?/? then replacement will not work.



In article ,
"Bob Phillips" wrote:

Selection.Replace What:=Char(160), Replacement:="-", _
Look:=clipart, SearchOrder:=xlByRows, MatchCase:=False

One way:

Public Sub ReplaceSpaces()
Dim rCell As Range
For Each rCell In Selection
With rCell
If InStr(1, Trim(.Text), " ") Then
.NumberFormat = "@"
.Value = Replace(Trim(.Text), " ", "-")
End If
End With
Next rCell
End Sub

If you're using XL97 or MacXL, change Replace(...) to
Application.Substitute(...)


In article ,
"biggyb75" wrote:

I am able to find by the white space but when it replaces with the - it
changes the format. It will not keep it as a text format.

I was able to figure it out. I used the "REPLACE function in excel. example
REPLACE(A2,2,1,"-"). If the cell was 1 1/64 it was replaced with 1-1/64.
Thanks for the help.

"JE McGimpsey" wrote:

Clipart? <g

Char() is an XL function, not VBA

Chr(160) is a non-breaking space. For a regular space character, use
Chr(32)

Perhaps

Selection.Replace _
What:=Chr(32), _
Replacement:="-", _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
MatchCase:=False

This assumes that the numbers are Text. If the numbers are simply
formatted as 0 ?/? then replacement will not work.



In article ,
"Bob Phillips" wrote:

Selection.Replace What:=Char(160), Replacement:="-", _
Look:=clipart, SearchOrder:=xlByRows, MatchCase:=False

Another way:

In an adjacent column:

=TEXT(A1,"0-?/?")

Copy down as far as necessary. Copy the column. Edit/Paste
Special/Values. Copy over, or delete, the original column.


In article ,
"biggyb75" wrote:

I am using Excel 2002. I have a spreadsheet set up as a retrival matricie
that I will upload into an AS400. In one of the columns there are dimensions
that are set up as whole number space fraction; ie 3 1/2. I need to replace
the space with a "-" so that the cell would read 3-1/2. Is there a way to do
that using "Find and Replace". One of the spreadsheets has over 7000 rows, I
don't want to have to do this by hand.