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

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
Edit>Copy
Select D1
Edit>Paste 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" <(E-Mail Removed)> wrote in message news:AE382C5D-99E0-4A77-B605-(E-Mail Removed)...
| 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

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
> Edit>Copy
> Select D1
> Edit>Paste 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" <(E-Mail Removed)> wrote in message news:AE382C5D-99E0-4A77-B605-(E-Mail Removed)...
> | 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
>
>
>

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" <(E-Mail Removed)> wrote in message
news:F896693D-F1B8-4AC8-B9A6-(E-Mail Removed)...
> 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
>> Edit>Copy
>> Select D1
>> Edit>Paste 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" <(E-Mail Removed)> wrote in message
>> news:AE382C5D-99E0-4A77-B605-(E-Mail Removed)...
>> | 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
>>
>>
>>

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" <(E-Mail Removed)> wrote in message
news:AE382C5D-99E0-4A77-B605-(E-Mail Removed)...
> 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