Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formatting Numbers
I need to delete the last two numbers shown in a series of cells. I can split the numbers at the hyphen if need be but second set of numbers needs to have the last two numbers eliminated (without the obvious backspacing). Copy of data: 000103-03101 000103-03102 000503-04601 000503-04701 001103-00601 000023-02901 000023-03001 000023-03002 000023-03003 For example: 000103-03101 needs to show 000103-031. I realize there will be duplicate cells. Please advise. Thanks. -- cen16868 ------------------------------------------------------------------------ cen16868's Profile: http://www.excelforum.com/member.php...o&userid=29521 View this thread: http://www.excelforum.com/showthread...hreadid=492220 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formatting Numbers
If your list is in Col_A, beginning in Cell A1: B1: =LEFT(A1,10) Copy that formula down as far as you need. If you want to convert those formulas to values: Select the Col_B formulas EditCopy EditPaste SpecialValues Does that help? Regards, Ron -- Ron Coderre ------------------------------------------------------------------------ Ron Coderre's Profile: http://www.excelforum.com/member.php...o&userid=21419 View this thread: http://www.excelforum.com/showthread...hreadid=492220 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formatting Numbers
If your numbers are consistently 12 characters (including hyphens) then in the cell next to the data, in this case B1. =LEFT(A1,10) Drag this down the list. Cheers, Steve -- SteveG ------------------------------------------------------------------------ SteveG's Profile: http://www.excelforum.com/member.php...fo&userid=7571 View this thread: http://www.excelforum.com/showthread...hreadid=492220 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formatting Numbers
With your data in column A, put this in B1 and copy down..........
=LEFT(A1,LEN(A1)-2) Vaya con Dios, Chuck, CABGx3 "cen16868" wrote: I need to delete the last two numbers shown in a series of cells. I can split the numbers at the hyphen if need be but second set of numbers needs to have the last two numbers eliminated (without the obvious backspacing). Copy of data: 000103-03101 000103-03102 000503-04601 000503-04701 001103-00601 000023-02901 000023-03001 000023-03002 000023-03003 For example: 000103-03101 needs to show 000103-031. I realize there will be duplicate cells. Please advise. Thanks. -- cen16868 ------------------------------------------------------------------------ cen16868's Profile: http://www.excelforum.com/member.php...o&userid=29521 View this thread: http://www.excelforum.com/showthread...hreadid=492220 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formatting Numbers
Select the column of numbers, then:
<Data <Text to Columns Click "Fixed Width", then <Next Click in the "Preview Window" to create the "Break Line". Make sure it's placed to separate the last 2 digits. Then <Next. In the "Preview Window", click in the second column containing your last 2 digits to select it. Click on "Do Not Import". The header of the second column should change to "Skip Column". Then <Finish And you're done! -- HTH, RD ================================================== === Please keep all correspondence within the Group, so all may benefit! ================================================== === "cen16868" wrote in message ... I need to delete the last two numbers shown in a series of cells. I can split the numbers at the hyphen if need be but second set of numbers needs to have the last two numbers eliminated (without the obvious backspacing). Copy of data: 000103-03101 000103-03102 000503-04601 000503-04701 001103-00601 000023-02901 000023-03001 000023-03002 000023-03003 For example: 000103-03101 needs to show 000103-031. I realize there will be duplicate cells. Please advise. Thanks. -- cen16868 ------------------------------------------------------------------------ cen16868's Profile: http://www.excelforum.com/member.php...o&userid=29521 View this thread: http://www.excelforum.com/showthread...hreadid=492220 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formatting Numbers
Thanks for all of your help! Formula's working great! MSR -- cen16868 ------------------------------------------------------------------------ cen16868's Profile: http://www.excelforum.com/member.php...o&userid=29521 View this thread: http://www.excelforum.com/showthread...hreadid=492220 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Seed numbers for random number generation, uniform distribution | Excel Discussion (Misc queries) | |||
Formatting numbers | Excel Discussion (Misc queries) | |||
conditional formatting question | Excel Discussion (Misc queries) | |||
Conditional formatting not available in Excel | Excel Discussion (Misc queries) | |||
Sorting imported "numbers" | Excel Discussion (Misc queries) |