function to eliminate a space

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a number of cells and I want to eliminate the spaces from each cell. The spaces can be in different locations. Is there a function or combined functions that will do this for me so I don’t have to do it one by one. Assume only one space not multiple space.

Example:
111 222 want 111222
11 22222 want 1122222

I think I need to use the Find,Len,Left/Right/Mid functions.
 
Edit / Replace / Replace what = " " (Just hit the space bar in the field,
Replace with = "" (Just leave blank).

For a formula, =SUBSTITUTE(A1," ","")

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------



eahoo970 said:
I have a number of cells and I want to eliminate the spaces from each cell.
Click to expand...
The spaces can be in different locations. Is there a function or combined
functions that will do this for me so I don't have to do it one by one. Assume
only one space not multiple space.
 
No, a substitute function will work, though it will remove all spaces. If
you only want to remove the first, middle or last space than that's
something else.

SUBSTITUTE(A1," ","")



eahoo970 said:
I have a number of cells and I want to eliminate the spaces from each
Click to expand...
cell. The spaces can be in different locations. Is there a function or
combined functions that will do this for me so I don't have to do it one by
one. Assume only one space not multiple space.
 
Why not use the "Replace" function (Ctl-H) with a space in
the upper entry and nothing in the lower.

This always does the trick.

Godd luck

M.
 
Back
Top