delete empty space after certain characters
 

Hi,
Just a quick question.
I have column D that always contained 4 letter characters. However, some of
the cells in that colum would have empty spaces following those characters.
For example, i can have something like this: "CYOD " or "DYED ".
How can i write a macro that would eliminate all the empty spaces after the
first 4 letters??

Thanks

delete empty space after certain characters
 

Insert a column to the right of D (if there isn't an empty one yet)
In( the new) E1, enter:

=LEFT(D1,4)

Copy down as far as you need.

You can use this column in your successive formulas, or you can replace the original column D with this new one:

Select column E
EditCopy
Select D1
EditPaste Special, check Values
You can now delete column E

Make a copy of your workbook before trying this

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"Don Doan" wrote in message ...
| Hi,
| Just a quick question.
| I have column D that always contained 4 letter characters. However, some of
| the cells in that colum would have empty spaces following those characters.
| For example, i can have something like this: "CYOD " or "DYED ".
| How can i write a macro that would eliminate all the empty spaces after the
| first 4 letters??
|
| Thanks

delete empty space after certain characters
 

oh thanks.. that's simple enough...
but would it possible to create a macro to look at every row in column D and
keep the first four letters and removed the rest??

"Niek Otten" wrote:

Insert a column to the right of D (if there isn't an empty one yet)
In( the new) E1, enter:

=LEFT(D1,4)

Copy down as far as you need.

You can use this column in your successive formulas, or you can replace the original column D with this new one:

Select column E
EditCopy
Select D1
EditPaste Special, check Values
You can now delete column E

Make a copy of your workbook before trying this

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"Don Doan" wrote in message ...
| Hi,
| Just a quick question.
| I have column D that always contained 4 letter characters. However, some of
| the cells in that colum would have empty spaces following those characters.
| For example, i can have something like this: "CYOD " or "DYED ".
| How can i write a macro that would eliminate all the empty spaces after the
| first 4 letters??
|
| Thanks

delete empty space after certain characters
 

Sub ClearSpaces()
Dim RngD As Range
Dim i As Range
Set RngD = Range("D2", Range("D" & Rows.Count).End(xlUp))
For Each i In RngD
i.Value = RTrim(i.Value)
Next i
End Sub
"Don Doan" wrote in message
...
oh thanks.. that's simple enough...
but would it possible to create a macro to look at every row in column D
and
keep the first four letters and removed the rest??

"Niek Otten" wrote:

Insert a column to the right of D (if there isn't an empty one yet)
In( the new) E1, enter:

=LEFT(D1,4)

Copy down as far as you need.

You can use this column in your successive formulas, or you can replace
the original column D with this new one:

Select column E
EditCopy
Select D1
EditPaste Special, check Values
You can now delete column E

Make a copy of your workbook before trying this

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"Don Doan" wrote in message
...
| Hi,
| Just a quick question.
| I have column D that always contained 4 letter characters. However,
some of
| the cells in that colum would have empty spaces following those
characters.
| For example, i can have something like this: "CYOD " or "DYED
".
| How can i write a macro that would eliminate all the empty spaces after
the
| first 4 letters??
|
| Thanks

delete empty space after certain characters
 

Or just use the TRIM function.

=TRIM(A1) will remove excess spaces in cell A1

--
Saruman

---------------------------------------------------------------------------
All Outgoing Mail Scanned By Symantec Antivirus 10
---------------------------------------------------------------------------
"Don Doan" wrote in message
...
Hi,
Just a quick question.
I have column D that always contained 4 letter characters. However, some
of
the cells in that colum would have empty spaces following those
characters.
For example, i can have something like this: "CYOD " or "DYED ".
How can i write a macro that would eliminate all the empty spaces after
the
first 4 letters??

Thanks