F
frank
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?
from all of them. Is there a quick way to do this?
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
J
JE McGimpsey
One way:
=ROUND(A1,-4)
=ROUND(A1,-4)
S
Simon Shaw
Or try:
=LEFT(A1,11)
note: assumes data is in cell A1
=LEFT(A1,11)
note: assumes data is in cell A1
J
JE McGimpsey
Note that this converts a number to Text.
To convert back to a number: = --LEFT(A1,11)
To convert back to a number: = --LEFT(A1,11)
R
RagDyer
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!
==============================================
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!
==============================================