Remove 1st 3 characters in each field of Excel column
 

Is there a single command/formula to remove the first 3 letters in each of
the fields in an entire column?

Data / Text To Columns / Fixed Width / Set break at 3 characters, remove all
other breaks, select first column and choose 'Do Not Import', Hit OK.

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------

"Chuck" wrote in message
...
Is there a single command/formula to remove the first 3 letters in each of
the fields in an entire column?

Try

=RIGHT(A1,LEN(A1)-3)

in an adjacent cell, then copy down as far as needed, then copy and paste
special as values.

You can also use datatext to columns and choose "Fixed width". Now click on
the window display to insert a line 3 characters in. If you click 'finish',
you'll have the 4th+ characters in the next column (you can also specify
where you want the results to go if you don't want them in the next column).
You can then delete the column with the original 3 letters.



"Chuck" wrote in message
...
Is there a single command/formula to remove the first 3 letters in each of
the fields in an entire column?

Chuck

Data in A2 enter in B2

=MID(A2,4,LEN(A2)-3)

And copy down

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
HIS

"Chuck" wrote in message
...
Is there a single command/formula to remove the first 3 letters in each of
the fields in an entire column?

Datatext to columns, fixed width click next, click after the 3rd character
and click next, select the left column and check do not import (skip), click
finish

another easy way would be to insert a new column adjacent to the one you
need to remove the characters from, assume the data is in A, if column B is
empty select the first adjacent cell, if it's not empty select the B column
and insert new column, the old B is now C, in B1 put

=MID(A1,4,255)

press enter, select B1 again, move the cursor to the lower right corenr of
B1 and when it changes from a fat cross to a thin cross double left click
with the mouse
and the formula will be copied down as long as there are values in A, while
still selected copy it, then do editpaste special as values in place.
Finally delete column A if it's not needed.

If you do this often you can record a macro when you do the text to columns


Regards,

Peo Sjoblom

"Chuck" wrote:

Is there a single command/formula to remove the first 3 letters in each of
the fields in an entire column?