Problem with TRIM function
Posted by Martin Basterfield on August 08, 2001 3:41 AM
Is there a way of using the TRIM command so as to only remove spaces from the rightmost of a cell, but to retain any doublespaces within a code? I am attempting to use the function to tidy up codes which typically look like this :-
AUA416---4SPIM--1-----
, where '-' signifies a space. The spaces after the final 1 are unwanted, but I need to keep the others. The TRIM command renders the above code as
AUA416-4SPIM-1
which isn't much help to me unfortunately!
Any suggestions would be greatly appreciated......
Posted by Aladin Akyurek on August 08, 2001 4:08 AM
Lets assume that these strings with extraneous spaces are in column A.
Activate Data|Text to Columns.
Follow the path "Delimited" & check "Space" as delimiter.
If you do this right, you'll get each string in pieces in columns A, B, and C.
In D1 enter: =A1&" "&B1&" "&C1 [ " " = double space ]
Copy down this formula as far as needed.
While in column D, do a Copy, activate A1 and do a Paste Special|Values.
Delete columns B, C, and D.
Aladin
===========
Posted by Martin Basterfield on August 08, 2001 5:05 AM
Very close Aladin, but when I try this I end up with two spaces between the first & second strings rather than the required three.
I've done some more investigating on the web since my first post, & I've come across mentions of a command called RTRIM which 'removes trailing spaces from a cell', which is exactly what I'm looking for, but I don't seem to have it on my version of Excel (97)! Presumably this must be some sort of add-in?
Martin
Posted by Aladin Akyurek on August 08, 2001 5:14 AM
No idea.
In the meantime, you can use
=A1&" "&B1&" "&C1
" " contains 3 spaces, the first proposal had 2. That is adjustable.
Aladin
Posted by mseyf on August 08, 2001 11:47 AM
Martin:
unfortunately, RTrim (and LTrim) are VBA commands, but if you're not adverse to using a UDF, you could try something like:
Function uTrim(cell)
uTrim = RTrim(cell.Value)
uTrim = LTrim(uTrim)
End Function
you can copy this into a VBA module and use the utrim() command in your sheet.
HTH
Mark
Posted by Mark W. on August 08, 2001 11:59 AM
Martin, Aladin was on the right track using the
Text to Columns wizard; however, I'd modify the
process as follows:
1. Select the cells containing the text that you
want to trim and choose the Data | Text to Columns...
menu command.
2. At Step 1 of 3 choose "Fixed width" and then
press [ Next> ].
3. At Step 2 of 3 remove the vertical lines in
Data preview window by double clicking on each,
and press [ Finish ].
That should do it!
Posted by Aladin Akyurek on August 08, 2001 12:31 PM
Martin, Aladin was on the right track using the
Was a bit uncertain about this step whether it could be understood (providing that it's worded clearly & tersely as you did). The reason why I resorted to the formula solution [ as I often do :) ].
Aladin
Posted by Ian on August 08, 2001 3:01 PM
Two sort of answer I'm not too happy with, Aladin maybe
as long as the sequence is the same, i.e. 6 characters 3 spaces (or -) 5 characters 2 spaces 1 number. try:
=SUBSTITUTE(LEFT($A$9,17),"-"," ") _(still figuring the 17 to be automatic)
or
=LEFT(A9,6)&" "&MID(A9,FIND("-",A9,1)+3,5)&" "&MID(A9,FIND("-",A9,10)+2,1) _(MESSY)
It's late and my reference is at work, but some tweeking and these will work for you (that where Aladin or some other expert comes in)
Ian
