delete empty space after certain characters

[Don Doan]

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

 

[Niek Otten]

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

| 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

 

[Don Doan]

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??

 

[Otto Moehrbach]

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

 

[Saruman]

Or just use the TRIM function.

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