Removing Spaces within text

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

Guest

Hi All,

I need to write some code to solve this problem.

I have a column of entries that I need to search through, and if it finds
any cells that have entries with a space - then remove the space and join
together the text on either side of the space.

For example:

abc123
def 456
ghi 789 jkl

.... the result in the column would then be

abc123
def456
ghi789jkl

Thanks very much for your help
Karen
 
Use the =SUBSTUTUTE() function:


=SUBSTITUTE(B7," ","") will the remove the spaces in B7, for example
 
Hi Karen,

The code below should sort out your problem:
Columns("I:I").Select
Range("I57").Activate
Selection.Replace What:=" ", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False

Where "I:I" is the column containing your data.

Hope it helps.

Cheers
Blaise
 
select the range of cells and do
Edit=>Replace

What: put in a space (hit the space bar once)
With: leave blank

Click replace all.

If you need a macro, turn on the macro recorder while you do it manually.
 
So how do you remove a space from an end of a string.

I need if a string ends with a space. Delete/remove it.
If string has a character other than a spac,e leave it alone.
 
You have a few options...

You could use:

dim myCell as range
dim myRng as Range
set myrng = selection
for each mycell in myrng.cells
mycell.value = rtrim(mycell.value)
next mycell

or getting rid of leading and trailing:
mycell.value = trim(mycell.value)

or getting rid of leading/trailing/duplicated embedded spaces:
mycell.value = application.trim(mycell.value)

======
A quick way way to get rid of leading/trailing spaces is to do:
select the range
data|text to columns
fixed width (remove any lines that excel guessed at)
finish up.

If you need a macro, record one when you do it manually.
 
Back
Top