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

B

biggyb75

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.
 
B

Bob Phillips

Best to do it with VBA

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

biggyb75

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.
 
J

JE McGimpsey

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.
 
J

JE McGimpsey

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

biggyb75

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.
 
J

JE McGimpsey

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.
 
Top