Trim blank space within text

C

celia

How can I remove the white space in text?

eg. "12( 35)" into "12(35)"
"12 3 5" into "1235"
"123 (5)" into "1235"

Possible to be done using TRIM function?

Thanks
 
A

Andy B

Celia

Try using the SUBSTITUTE function. Using this you can substitute all of the
spaces with ""

Andy.
 
D

David McRitchie

To remove all spaces from a selection of constants (not formulas),
you can use a macro, if you will be doing this a lot.

see "RemoveAllSpaces" macro just below the TRIMALL macro
Rearranging Data in Columns (and reformatting)
http://www.mvps.org/dmcritchie/excel/join.htm#removeallspaces

Directions to install and use a macro on my getstarted.htm web page.

You may confuse yourself if you call removing all spaces
trimming since the Excel and VBA TRIM functions remove
spaces from right and/or left and the Excel TRIM function
remove duplicate spaces within.
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

Andy B said:
Celia

Try using the SUBSTITUTE function. Using this you can substitute all of the
spaces with ""

Andy.
Click to expand...
 
J

jammy

for just trimming all whitespace from a spreadsheet you could jus
highlight the column/row/selection you want, then

ctrl-h (replace)
find what: " " (just a space without the quotes)
replace with: "" (nothing at all)
click replace all

and bob's your uncl
 
D

David McRitchie

Jammy's manual solution (ctrl+H) is better than use of the
SUBSTITUTE Worksheet Function which requires
a helper column.

When you use the Ctrl+H, you may also want to
convert ASCII 160 character to an empty string
in this case just like you do for a real space..

The macro I suggested does this automatically as well.
The Char(160) is what you might get from copying from
a web page used to force a space it is a non breaking
space character ( )
 
C

celia

Hi, David

How can I modify/arrange the code so that it is effective in cel
A12:A52 only?

Please let me know. Thanks.

Celi
 
D

David McRitchie

Hi Celia,
change both occurrences of
Selection.SpecialCells(
to
Range(A12:A52).SpecialCells(
 
Top