Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
remove spaces in text in excel | Excel Discussion (Misc queries) | |||
Column A is Town, Column B is names. How can Excel add & tell how. | Charts and Charting in Excel | |||
Remove link fr a column of entries | Excel Discussion (Misc queries) | |||
Set up barcode column in Excel | Excel Worksheet Functions | |||
How do I sort a column of formulas in Excel? | Excel Discussion (Misc queries) |