Home |
Search |
Today's Posts |
#1
|
|||
|
|||
help with removing digits from a number
I have a column with 15 digit numbers. I need to remove the last four digits
from all of them. Is there a quick way to do this? |
#2
|
|||
|
|||
One way:
=ROUND(A1,-4) In article , "frank" wrote: I have a column with 15 digit numbers. I need to remove the last four digits from all of them. Is there a quick way to do this? |
#3
|
|||
|
|||
Or try:
=LEFT(A1,11) note: assumes data is in cell A1 "frank" wrote: I have a column with 15 digit numbers. I need to remove the last four digits from all of them. Is there a quick way to do this? |
#4
|
|||
|
|||
Note that this converts a number to Text.
To convert back to a number: = --LEFT(A1,11) In article , "Simon Shaw" <simonATsimonstoolsDOTcom wrote: Or try: =LEFT(A1,11) note: assumes data is in cell A1 "frank" wrote: I have a column with 15 digit numbers. I need to remove the last four digits from all of them. Is there a quick way to do this? |
#5
|
|||
|
|||
If you're going to have to do this on a regular basis, then the use of
formulas, such as: = --LEFT(A1,11) would probably be the best way, since importing or pasting to Column A would automatically return your revised numbers. BTW, you realize that you will still have *formulas* in the returning column, and not plain numbers. An option that will return actual numbers, where it may not be necessary to remove the converting formulas, is to use TTC (Text To Columns). With TTC, you have the option to actually replace the original numbers with revised numbers, or return real, revised numbers to another column, leaving the originals untouched, if so desired. Select the column of original data, then: <Data <TextToColumns And click "Fixed Width", then <Next. Click in the "Preview" window, at the appropriate place, to create a "break line" to segregate the last 4 digits, Then <Next. Now, you have options. If you click <Finish, the 11 digit column will replace your original data, and the 4 digit column will be returned to the next, adjoining column. To eliminate the 4 digits and have the 11 replace the original: The 11 digit column is selected by default. Change the selection to the 4 digit column, then click on "Do Not Import", which changes the column header in the preview window from "General" to "Skip". Then click <Finish. If you wish to retain the original, *before* clicking <Finish, you could *also* click in the "Destination" window, which by default contains the address of the original column of data, and change the address in there to any other column, so that the 11 digit column will display in it's own column, and not replace the original. -- HTH, RD ============================================== Please keep all correspondence within the Group, so all may benefit! ============================================== "frank" wrote in message ... I have a column with 15 digit numbers. I need to remove the last four digits from all of them. Is there a quick way to do this? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how do i increase the number of digits displayed | Excel Discussion (Misc queries) | |||
How do I identify the 7th digit in a 13 digit number, then establi | Excel Worksheet Functions | |||
Least number of digits in Y-axis labels | Charts and Charting in Excel | |||
How do I format cells to a specific number of digits? | Excel Discussion (Misc queries) | |||
GET.CELL | Excel Worksheet Functions |