Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Trying to truncate or separate the first 3 characters/digits of co
I have a spreadsheet with a column of numbers. I'm trying to extract /
truncate / remove the leading 3 digits in each cell. The numbers vary in length, so using the LEFT or RIGHT function doesn't seem to be an option. The numbers represent division / dept. numbers within my company. The leading 3 digits represent the division, which I want to remove / discard. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Trying to truncate or separate the first 3 characters/digits of co
=RIGHT(TEXT(A1,"0"),LEN(TEXT(A1,"0"))-3)
-- Kind regards, Niek Otten "Jim" wrote in message ... I have a spreadsheet with a column of numbers. I'm trying to extract / truncate / remove the leading 3 digits in each cell. The numbers vary in length, so using the LEFT or RIGHT function doesn't seem to be an option. The numbers represent division / dept. numbers within my company. The leading 3 digits represent the division, which I want to remove / discard. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Trying to truncate or separate the first 3 characters/digits of co
Hi
Try something like =MID(A1,4,LEN(A1)-3) Andy. "Jim" wrote in message ... I have a spreadsheet with a column of numbers. I'm trying to extract / truncate / remove the leading 3 digits in each cell. The numbers vary in length, so using the LEFT or RIGHT function doesn't seem to be an option. The numbers represent division / dept. numbers within my company. The leading 3 digits represent the division, which I want to remove / discard. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Trying to truncate or separate the first 3 characters/digits of co
Or if you want to retain decimals
=RIGHT(A2,LEN(A2)-3) Regards Peter Atherton "Jim" wrote: I have a spreadsheet with a column of numbers. I'm trying to extract / truncate / remove the leading 3 digits in each cell. The numbers vary in length, so using the LEFT or RIGHT function doesn't seem to be an option. The numbers represent division / dept. numbers within my company. The leading 3 digits represent the division, which I want to remove / discard. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Trying to truncate or separate the first 3 characters/digits of co
Thanks to all of you!
Niek - your formula worked for some of the entries, but for some cells the result wasn't quite right. Example: returned last TWO digits on 7-digit cells. I'm sure it would work with a slight tweak. Andy - worked perfectly Peter - worked perfectly. "Jim" wrote: I have a spreadsheet with a column of numbers. I'm trying to extract / truncate / remove the leading 3 digits in each cell. The numbers vary in length, so using the LEFT or RIGHT function doesn't seem to be an option. The numbers represent division / dept. numbers within my company. The leading 3 digits represent the division, which I want to remove / discard. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Separate first and second name in one cell into separate cells. | Excel Discussion (Misc queries) | |||
Open Excel files in separate sessions, not just separate windows? | Excel Discussion (Misc queries) | |||
Open separate window | Excel Worksheet Functions | |||
Divide worsheet and save as separate files | Excel Worksheet Functions | |||
Separate first and second name in one cell into separate cells. | Excel Discussion (Misc queries) |