Removing Spaces within text
 

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

Removing Spaces within text
 

Use the =SUBSTUTUTE() function:


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

--
Gary's Student


"KAREN27" wrote:

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

Removing Spaces within text
 

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

"KAREN27" wrote:

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

Removing Spaces within text
 

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.

--
Regards,
Tom Ogilvy

"KAREN27" wrote in message
...
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

Removing Spaces within text
 

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.
--
Thank you for your time.
Windows XP
Office 2002


"BlaiseW" wrote:

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

"KAREN27" wrote:

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

Removing Spaces within text
 

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.

James Kendall wrote:

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.
--
Thank you for your time.
Windows XP
Office 2002

"BlaiseW" wrote:

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

"KAREN27" wrote:

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

--

Dave Peterson