Deleting first 5 characters in a column of cells ?

Posted by Rick M on February 08, 2001 6:52 AM

I have a column (1522 rows long) I would like to delete the first 4 characters ab=nd the space:
I have:
CUST 00001
CUST 10227
I want:
00001
10027

Thanks

Posted by Mark W. on February 08, 2001 6:58 AM

Use the Data Text to Columns... menu command.

Posted by Bruce on February 08, 2001 9:27 AM

CTL-H will bring up the find and replace menu. IN the find box type "CUST " without the quotes, leave the replace blank, and select replace all.

Posted by Bruce on February 08, 2001 9:29 AM

CTL-H will bring up the find and replace menu. In the find box type "CUST " without the quotes, leave the replace blank, and select replace all.

Posted by Dave Hawley on February 08, 2001 4:46 PM

I think Bruces idea of Edit replace would be easiest.

Dave

OzGrid Business Applications

Posted by Mark W. on February 08, 2001 6:49 PM

Oh, yeah? That's only because you hadn't considered
the possibilty that the cell format for this column
of values might be General as would be the case if
Rick was opening a .csv file. Under these circum-
stances an Edit Replace... will not only strip off
the leading characters but will convert the strings
to numeric values resulting in the loss of the 1st
value's leading zeroes. You end up with 1 instead
of "00001" as was desired by Rick.

This is the value of the Text to Column wizard (or any
wizard for that matter). It forces the uninitiated
to think (step by step) about what they're doing.

1. At Step 1 of 3, you choose "Fixed width" and press
"Next>"
2. At Step 2 of 3, you press "Next>"
3. At Step 3 of 3, you assign the "Do not import
column (skip)" format to the 1st parsed column;
then "Text" to the 2nd; and press "Finish"

It's both easy and fool proof!

Posted by Dave Hawley on February 08, 2001 9:52 PM

>Oh, yeah?
LOL, my 5 year old daughter uses that expression.

>It forces the uninitiated
to think (step by step) about what they're doing.

So what happened to you? :-)

Rick, push Ctrl+H, type text to remove, then put ' in the Replace with box.

Can't get easier than that :)

Dave

OzGrid Business Applications

Posted by Mark W. on February 09, 2001 6:35 AM

>Oh, yeah?
>>LOL, my 5 year old daughter uses that expression.

So she's as skeptical as I am -- good for her!

>It forces the uninitiated to think (step by step)
>about what they're doing.
>>So what happened to you? :-)

Hey, I was the one who anticipated the pitfall...

>Rick, push Ctrl+H, type text to remove, then put
>' in the Replace with box. Can't get easier than
>that :)

Now, you can deal with Rick's next stuggle. How
to deal of that apostrophe when he doesn't want it
any more. Well, I suppose that's job security...

Posted by Dave Hawley on February 09, 2001 2:27 PM

Mark, I've already suugested to Rick how to deal with the apostrophe in front of his formula and it works just fine on my PC, I know because I tried (unlike some :O).

Mark is saying "I'm wrong" that hard for you to do. I've had to say it many times, even to you not so long ago.

Dave

OzGrid Business Applications