how to limit amount of numbers in cells in excel

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am trying to limit the amount of numbers that are in a cell. For example I
have a cell that has this sort of number 1220H123456, and I only want to have
the last six digits in that cell so I can sort in a numerical order. I have
been deleting the first 5 digits manually, but that takes way too much time
when the list has over 3000 rows. Any help would be wonderful!!!

Jeff
 
Use a "helper" column which contains a text formula to parse out the
beginning characters.
Then you can sort on that helper column.

Data in A1 to A3000.
Enter this in B1:

=RIGHT(A1,6)

Drag down to copy,
OR
*Double* click the fill handle on B1, to *automatically* copy the formula
down Column B, as far as there is data in Column A.
 
Select the entire column.

Go to Data|Text to Columns and select Fixed Width radio button.

Click Next and in the white area, click just before the 5th last digit
in one of the numbers. You should see a vertical line separating the
numbers. You can drag the line over if you missed the point of
separation.

Click Finish. The numbers should be separated into 2 columns. Now you
can delete or hide the unwanted column of numbers.
 
Vito - I think you hit on the formatting in my spreadsheet that I have been
trying to get rid of. I have a vertical line within certain cells in several
columns. I used your instructions below and it looks like the vertical line
is a delimited character. How do I get rid of it so I can copy and paste
these cells without the vertical line?
 
I don't think Vito is coming back after 3 years.

How about:
=SUBSTITUTE(A1,"|","New Text")

Or if you are trying to spread the data to various columns, splitting
it up at the vertical line, just use Text To Columns, Delimited, with
| as the delimiter.
 
Back
Top