Remove space from text in cell

[Guest]

First, I have a confession to make: I actually searched the available answers
first (using search text: "remove space from text in cell"). Nothing there
fit my simpler problem:

Cell G3 contains "Herkimer, Mortimer". I need to get rid of that space
("Herkimer,Mortimer") becaise I am about to search with the result into a
different WB where the data is stored without spaces.

My bet is that there's a really simple function for this <g>!

 

[JW]

Do you have to have this done in VBA? If not, there is a very simple
built-in function you can use to acheive this. Assuming your text is
in cell A1, this formula will remove all empty spaces.
=SUBSTITUTE(A1," ","")

HTH

 

[Guest]

Dave,

If it's specific enough that you're looking a comma followed by a space,
maybe the SUBSTITUTE function can help you.

=SUBSTITUTE(G3, ", ", ",")

 

[Guest]

That there's the puppy I've been looking for!!! Bets the heck out of this mess:

=TRIM(LEFT(G4, FIND(", ",G4)))&MID(G4, (FIND(", ",G4)+2),12)

Thankee both!

 

[Guest]

Hi Dave,

If you're looking for a simple worksheet formula then SUBSTITUTE should work.

For your example write in a cell:
=SUBSTITUTE(G3," ","")



If you're using VBA then lookup the REPLACE method in the VBA help, that
should work for you.

Sean.