Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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. |
#2
|
|||
|
|||
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. |
#3
|
|||
|
|||
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. |
#4
|
|||
|
|||
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. |
#5
|
|||
|
|||
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 |
#6
|
|||
|
|||
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. |
#7
|
|||
|
|||
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 |
#8
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
HOW DO I TOGGLE FROM FIND AND REPLACE TO A CELL EASILY? | Excel Discussion (Misc queries) | |||
find and replace cell reference to a new worksheet | Excel Worksheet Functions | |||
How can I do a find and replace for a cell that has multiple line. | Excel Discussion (Misc queries) | |||
How Can I find and replace symbols in excel data ( white square) | Excel Discussion (Misc queries) | |||
VB Find and Replace | Excel Worksheet Functions |