Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
Jim
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.misc
Niek Otten
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.misc
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.misc
PeterAtherton
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.misc
Jim
 
Posts: n/a
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Separate first and second name in one cell into separate cells. Dwight in Georgia Excel Discussion (Misc queries) 3 January 25th 06 10:09 PM
Open Excel files in separate sessions, not just separate windows? Bob at Dexia Design Excel Discussion (Misc queries) 1 October 18th 05 05:46 PM
Open separate window Robbin Excel Worksheet Functions 2 September 1st 05 01:03 PM
Divide worsheet and save as separate files Colin Hayes Excel Worksheet Functions 0 June 28th 05 09:42 PM
Separate first and second name in one cell into separate cells. Gary's Student Excel Discussion (Misc queries) 0 April 27th 05 11:11 PM


All times are GMT +1. The time now is 06:18 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"