Home |
Search |
Today's Posts |
#1
|
|||
|
|||
How do I strip out some parts of a column of text data?
I have a column of text data, which happens to be 11 characters wide. I want
to strip the right-most 6 characters out of the entire column, without having to do each cell one at a time. For example I want to to from this data: 2039 041175 to this data: 2039 for all 350 or so rows of data in the column on the Excel sheet. |
#2
|
|||
|
|||
Use a helper column of formulas
=right(a1,6) will return the value as text =--right(a1,6) will return the value as numeric Just copy that down the helper column. footballcmr2 wrote: I have a column of text data, which happens to be 11 characters wide. I want to strip the right-most 6 characters out of the entire column, without having to do each cell one at a time. For example I want to to from this data: 2039 041175 to this data: 2039 for all 350 or so rows of data in the column on the Excel sheet. -- Dave Peterson |
#3
|
|||
|
|||
To pull the leftmost four characters of cell A1, set up a dummy column and
use the formula =Left(A1, 4) [change the cell name as appropriate]. Now copy down to the last row. Finally, Copy the converted cells and, in the same location, Paste Special Values (this will eliminate the formula). "footballcmr2" wrote: I have a column of text data, which happens to be 11 characters wide. I want to strip the right-most 6 characters out of the entire column, without having to do each cell one at a time. For example I want to to from this data: 2039 041175 to this data: 2039 for all 350 or so rows of data in the column on the Excel sheet. |
#4
|
|||
|
|||
Oops. I thought you wanted to keep the rightmost 6 characters.
Sorry. Dave Peterson wrote: Use a helper column of formulas =right(a1,6) will return the value as text =--right(a1,6) will return the value as numeric Just copy that down the helper column. footballcmr2 wrote: I have a column of text data, which happens to be 11 characters wide. I want to strip the right-most 6 characters out of the entire column, without having to do each cell one at a time. For example I want to to from this data: 2039 041175 to this data: 2039 for all 350 or so rows of data in the column on the Excel sheet. -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Count Position of Filtered TEXT cells in a column | Excel Worksheet Functions | |||
Count Position of Filtered TEXT cells in a column | Excel Worksheet Functions | |||
Return Count for LAST NonBlank Cell in each Row | Excel Worksheet Functions | |||
up to 7 functions? | Excel Worksheet Functions | |||
Counting NUMBERS &/or TEXT from a column to other sheet ? | Excel Worksheet Functions |