|
Changing numbers
I have about 5000 product code entries, but they all need changing, i want to get rid of the last two digits: e.g 01234500 to 012345 Is there a way to do this for all the entries quickly? -- cj21 ------------------------------------------------------------------------ cj21's Profile: http://www.excelforum.com/member.php...o&userid=25673 View this thread: http://www.excelforum.com/showthread...hreadid=391238 |
If all numbers are the same number of chaacters you could use the following formula to get rid of the 0's If 01234500 is in cell A1 then in cell B1 enter =MID(A1,1,6) where 1 is the number of the first character to include and 6 is the number of characters to include, so in this example it would return the value 012345 Paul -- Paul Sheppard ------------------------------------------------------------------------ Paul Sheppard's Profile: http://www.excelforum.com/member.php...o&userid=24783 View this thread: http://www.excelforum.com/showthread...hreadid=391238 |
If the numbers have varying numbers of characters you could use the following formula to get rid of the 0's If the number is in cell A1 then in cell B1 enter =MID(A1,1,LEN(A1)-2) If the number is 01234500 it will return the value 012345 If the number is 012345678900 it will return the value 012456789 Paul -- Paul Sheppard ------------------------------------------------------------------------ Paul Sheppard's Profile: http://www.excelforum.com/member.php...o&userid=24783 View this thread: http://www.excelforum.com/showthread...hreadid=391238 |
Paul you are a genius, that's saved me alot of work. Chris -- cj21 ------------------------------------------------------------------------ cj21's Profile: http://www.excelforum.com/member.php...o&userid=25673 View this thread: http://www.excelforum.com/showthread...hreadid=391238 |
Is there a way to get rid of all the numbers except the last 2. The number
lengths vary from 6 to 8 digits? Thanks "Paul Sheppard" wrote: If the numbers have varying numbers of characters you could use the following formula to get rid of the 0's If the number is in cell A1 then in cell B1 enter =MID(A1,1,LEN(A1)-2) If the number is 01234500 it will return the value 012345 If the number is 012345678900 it will return the value 012456789 Paul -- Paul Sheppard ------------------------------------------------------------------------ Paul Sheppard's Profile: http://www.excelforum.com/member.php...o&userid=24783 View this thread: http://www.excelforum.com/showthread...hreadid=391238 |
You could do a right align (highlight column) and then under DATA select
"Text To Columns". select the option "fixed with" click "next" click in the data preview area where you want to cut off (last two numbers) click next. In the data preview area select the side of the data that you do not want (left side) click on the "do not import column (skip)" option. Click on the right side; click the "text" option and then "Finish" "SCW" wrote: Is there a way to get rid of all the numbers except the last 2. The number lengths vary from 6 to 8 digits? Thanks "Paul Sheppard" wrote: If the numbers have varying numbers of characters you could use the following formula to get rid of the 0's If the number is in cell A1 then in cell B1 enter =MID(A1,1,LEN(A1)-2) If the number is 01234500 it will return the value 012345 If the number is 012345678900 it will return the value 012456789 Paul -- Paul Sheppard ------------------------------------------------------------------------ Paul Sheppard's Profile: http://www.excelforum.com/member.php...o&userid=24783 View this thread: http://www.excelforum.com/showthread...hreadid=391238 |
Thank you.
"Lisa Files" wrote: You could do a right align (highlight column) and then under DATA select "Text To Columns". select the option "fixed with" click "next" click in the data preview area where you want to cut off (last two numbers) click next. In the data preview area select the side of the data that you do not want (left side) click on the "do not import column (skip)" option. Click on the right side; click the "text" option and then "Finish" "SCW" wrote: Is there a way to get rid of all the numbers except the last 2. The number lengths vary from 6 to 8 digits? Thanks "Paul Sheppard" wrote: If the numbers have varying numbers of characters you could use the following formula to get rid of the 0's If the number is in cell A1 then in cell B1 enter =MID(A1,1,LEN(A1)-2) If the number is 01234500 it will return the value 012345 If the number is 012345678900 it will return the value 012456789 Paul -- Paul Sheppard ------------------------------------------------------------------------ Paul Sheppard's Profile: http://www.excelforum.com/member.php...o&userid=24783 View this thread: http://www.excelforum.com/showthread...hreadid=391238 |
Hi SCW Another way to extract the last 2 digits would be to use this formula in the cell next to the numbers you want to change =IF(LEN(A3)=6,MID(A3,5,2),IF(LEN(A3)=7,MID(A3,6,2) ,MID(A3,7,2))) Paul -- Paul Sheppard ------------------------------------------------------------------------ Paul Sheppard's Profile: http://www.excelforum.com/member.php...o&userid=24783 View this thread: http://www.excelforum.com/showthread...hreadid=391238 |
| All times are GMT +1. The time now is 12:41 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com