Removing part of a number in Excel

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

Guest

I'm trying to make a 9 digit number a 5 digit number by removing the first
four numbers. I have several lines of data and would like to do this in one
or two steps.

Example: 123456789 to end up looking like 56789
 
Try this:

Select the range of numbers

From the Excel main menu:
<data><text-to-columns>
Check: Fixed width......click [Next]
Click between the 4th and 5th digit
(that will create a separation point)......click [Next]
Select the first column
Check: Do not import column(skip)

If you want to replace the original numbers with the truncated numbers,
leave the Destination reference as the first cell in the number range.
Otherwise, select a new destination for the truncated list.
Click the [Finish] button

Does that help?
***********
Regards,
Ron

XL2002, WinXP
 
Ron said:
Try this:

Select the range of numbers

From the Excel main menu:
<data><text-to-columns>
Check: Fixed width......click [Next]
Click between the 4th and 5th digit
(that will create a separation point)......click [Next]
Select the first column
Check: Do not import column(skip)

If you want to replace the original numbers with the truncated numbers,
leave the Destination reference as the first cell in the number range.
Otherwise, select a new destination for the truncated list.
Click the [Finish] button

Does that help?
***********
Regards,
Ron

XL2002, WinXP


RISXRAY said:
I'm trying to make a 9 digit number a 5 digit number by removing the first
four numbers. I have several lines of data and would like to do this in one
or two steps.

Example: 123456789 to end up looking like 56789
Click to expand...
Click to expand...

The easiest way is:

=mid(A1,5,5) A1 being the cell reference wher the number sits. In this
example the mid formular counts 5 characters from the left and then
returns the next five characters into the cell the formular is written
in.

Regards,

John
 
Back
Top