Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Chuck
 
Posts: n/a
Default 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?
  #2   Report Post  
Ken Wright
 
Posts: n/a
Default

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?



  #3   Report Post  
Dave R.
 
Posts: n/a
Default

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?



  #4   Report Post  
Nick Hodge
 
Posts: n/a
Default

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?



  #5   Report Post  
Peo Sjoblom
 
Posts: n/a
Default

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?

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
remove spaces in text in excel GnarlyCar Excel Discussion (Misc queries) 3 February 1st 05 06:02 PM
Column A is Town, Column B is names. How can Excel add & tell how. Cindy Charts and Charting in Excel 3 January 13th 05 08:27 PM
Remove link fr a column of entries Rasoul Khoshravan Azar Excel Discussion (Misc queries) 1 December 17th 04 07:44 PM
Set up barcode column in Excel CareerPro-Naperville Resumes Excel Worksheet Functions 1 December 4th 04 06:37 AM
How do I sort a column of formulas in Excel? Gordon Excel Discussion (Misc queries) 0 November 26th 04 04:19 PM


All times are GMT +1. The time now is 09:09 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"