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.

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

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.

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