trying to replace 2 spaces with 1

[mgm]

Is there a way to search and replace within a column where there are 2
spaces present? When I search for " " (no quotes) it says cannot be found.

Thanks!
mgm

 

[T. Valko]

It works ok for me.

A1 = try<space><space>this
A2 = lets try this
A3 = try it

Select A1:A3
Goto Edit>Replace
Find what: hit the space bar twice
Replace with: hit the space bar once
Replace all

Results:

A1 = try this
A2 = lets try this
A3 = try it

Biff

 

[Guest]

Maybe someone else can respond with the technical explanation ("ansi
characters . . .") of why this works, but here's my approach:

- In some unused cell, type two spaces
- Highlight what you just typed and press Ctrl-C to copy
- Open the Replace function and paste (Ctrl-V) into the "Find What" field
- Paste again into the "Replace With" field, then hit backspace to eliminate
one of the two spaces.

If you want to limit your Replace to just one column, highlight that column
before invoking the Replace function.

HTH

 

[Niek Otten]

You may have Non-Breaking Spaces in the cell. You can check with the Code() function
Look here for code to remove them:

http://www.mvps.org/dmcritchie/excel/join.htm#trimall

--
Kind regards,

Niek Otten
Microsoft MVP - Excel


| Is there a way to search and replace within a column where there are 2
| spaces present? When I search for " " (no quotes) it says cannot be found.
|
| Thanks!
| mgm
|
|

 

[Niek Otten]

BTW, the TRIM() function reduces multiple spaces to single ones (and removes leading and trailing spaces)

--
Kind regards,

Niek Otten
Microsoft MVP - Excel


| You may have Non-Breaking Spaces in the cell. You can check with the Code() function
| Look here for code to remove them:
|
| http://www.mvps.org/dmcritchie/excel/join.htm#trimall
|
| --
| Kind regards,
|
| Niek Otten
| Microsoft MVP - Excel
|
|
|| Is there a way to search and replace within a column where there are 2
|| spaces present? When I search for " " (no quotes) it says cannot be found.
||
|| Thanks!
|| mgm
||
||
|
|

 

[Bob Umlas]

Another thing to look for -- make sure you don't have the checkbox "Match
Entire Cell Contents" selected! It could have been selected from a previous
Find/Replace.
Bob Umlas
Excel MVP