delete trailing space in column
 

B2:B10000 consist of what supposed to be dates.
The only problem is, when I attempt to change format, it wont take.
Found out the problem is a trailing space at end.

I have tried several delete space codes, but to no avail.

Please.... any suggestions to delete trailing spaces?

2009-05-02*

delete trailing space in column
 

Have you tried =TRIM(ref)? In a column next to these dates, insert this
formula and copy down. Then copy the trimmed data and paste special as values
Then delete the incorrect column
--
HTH

Kassie

Replace xxx with hotmail


"J.W. Aldridge" wrote:

B2:B10000 consist of what supposed to be dates.
The only problem is, when I attempt to change format, it wont take.
Found out the problem is a trailing space at end.

I have tried several delete space codes, but to no avail.

Please.... any suggestions to delete trailing spaces?

2009-05-02

delete trailing space in column
 

If familiar with VBA you could write a loop using the Trim function.

Drop a button on the sheet and paste in the following... (Note will
need to substitute "Sheet1" with the name of your sheet.)

Private Sub CommandButton1_Click()

Dim intRow as integer
Dim strCell as string

intRow = 2
intCol = "B"
strCell = "B" & Cstr(intRow)

Do While Len(strCell) 0
Worksheets("Sheet1").Range(strCell) = Trim(Worksheets
("Sheet1").Range(strCell))
intRow = intRow + 1
strCell = intCol & Cstr(intRow)
Loop

End sub

Good luck!

On Apr 27, 1:31*pm, "J.W. Aldridge"
wrote:
B2:B10000 consist of what supposed to be dates.
The only problem is, when I attempt to change format, it wont take.
Found out the problem is a trailing space at end.

I have tried several delete space codes, but to no avail.

Please.... any suggestions to delete trailing spaces?

2009-05-02*

delete trailing space in column
 

was trying to go with vba, however vba code didn't work. may resort to
trim. thanx all

delete trailing space in column
 

trim didnt work either

delete trailing space in column
 

Is this data imported from Access by any chance?

J.W. Aldridge;324225 Wrote:
trim didnt work either


--
The Code Cage Team

Regards,
The Code Cage Team
'The Code Cage' (http://www.thecodecage.com)
------------------------------------------------------------------------
The Code Cage Team's Profile: http://www.thecodecage.com/forumz/member.php?userid=2
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=90578

delete trailing space in column
 

Select the column
Edit|replace
what: (space character)
with: (leave blank)
replace all

If you copied these dates from the web, you may have those HTML non-breaking
spaces in your data.

Change that what to:
What: alt-0160
Hit and hold the alt key while typing the 0160 on the numeric keypad.

==========
Another option would be to
select the column
Data|Text to columns
Fixed width
Draw a line after the last digit
Choose date for the first field (ymd???)
and skip for the second field
and finish up the wizard.

Then format the cells the way you like.


"J.W. Aldridge" wrote:

B2:B10000 consist of what supposed to be dates.
The only problem is, when I attempt to change format, it wont take.
Found out the problem is a trailing space at end.

I have tried several delete space codes, but to no avail.

Please.... any suggestions to delete trailing spaces?

2009-05-02

--

Dave Peterson

delete trailing space in column
 

That html thingy was it...

Tried the alt-0160 and it worked.

Thanx!