Trim blank space within text

[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

 

[Andy B]

Celia

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

Andy.

 

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

Celia

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

Andy.

 

[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

 

[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 (&nbsp

 

[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

 

[David McRitchie]

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