Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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* |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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* |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
delete trailing space in column
was trying to go with vba, however vba code didn't work. may resort to
trim. thanx all |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
delete trailing space in column
trim didnt work either
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
delete trailing space in column
That html thingy was it...
Tried the alt-0160 and it worked. Thanx! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Removing leading and trailing space... | Excel Discussion (Misc queries) | |||
How do I delete an extra space in every cell in a column? | Excel Discussion (Misc queries) | |||
(in)definite article and leading/trailing space remover needed | Excel Programming | |||
Trailing Space / Format Question | Excel Discussion (Misc queries) | |||
Delete leading or trailing blank spaces in cell - an example | Excel Programming |