Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Jeff
 
Posts: n/a
Default how to limit amount of numbers in cells in excel

I am trying to limit the amount of numbers that are in a cell. For example I
have a cell that has this sort of number 1220H123456, and I only want to have
the last six digits in that cell so I can sort in a numerical order. I have
been deleting the first 5 digits manually, but that takes way too much time
when the list has over 3000 rows. Any help would be wonderful!!!

Jeff
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Ragdyer
 
Posts: n/a
Default how to limit amount of numbers in cells in excel

Use a "helper" column which contains a text formula to parse out the
beginning characters.
Then you can sort on that helper column.

Data in A1 to A3000.
Enter this in B1:

=RIGHT(A1,6)

Drag down to copy,
OR
*Double* click the fill handle on B1, to *automatically* copy the formula
down Column B, as far as there is data in Column A.
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Jeff" wrote in message
...
I am trying to limit the amount of numbers that are in a cell. For example

I
have a cell that has this sort of number 1220H123456, and I only want to

have
the last six digits in that cell so I can sort in a numerical order. I

have
been deleting the first 5 digits manually, but that takes way too much

time
when the list has over 3000 rows. Any help would be wonderful!!!

Jeff


  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Vito
 
Posts: n/a
Default how to limit amount of numbers in cells in excel


Select the entire column.

Go to Data|Text to Columns and select Fixed Width radio button.

Click Next and in the white area, click just before the 5th last digit
in one of the numbers. You should see a vertical line separating the
numbers. You can drag the line over if you missed the point of
separation.

Click Finish. The numbers should be separated into 2 columns. Now you
can delete or hide the unwanted column of numbers.


--
Vito
------------------------------------------------------------------------
Vito's Profile: http://www.excelforum.com/member.php...o&userid=29182
View this thread: http://www.excelforum.com/showthread...hreadid=497176

  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 9
Default how to limit amount of numbers in cells in excel

Vito - I think you hit on the formatting in my spreadsheet that I have been
trying to get rid of. I have a vertical line within certain cells in several
columns. I used your instructions below and it looks like the vertical line
is a delimited character. How do I get rid of it so I can copy and paste
these cells without the vertical line?

"Vito" wrote:


Select the entire column.

Go to Data|Text to Columns and select Fixed Width radio button.

Click Next and in the white area, click just before the 5th last digit
in one of the numbers. You should see a vertical line separating the
numbers. You can drag the line over if you missed the point of
separation.

Click Finish. The numbers should be separated into 2 columns. Now you
can delete or hide the unwanted column of numbers.


--
Vito
------------------------------------------------------------------------
Vito's Profile: http://www.excelforum.com/member.php...o&userid=29182
View this thread: http://www.excelforum.com/showthread...hreadid=497176


  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 622
Default how to limit amount of numbers in cells in excel

I don't think Vito is coming back after 3 years.

How about:
=SUBSTITUTE(A1,"|","New Text")

Or if you are trying to spread the data to various columns, splitting
it up at the vertical line, just use Text To Columns, Delimited, with
| as the delimiter.


  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 9
Default how to limit amount of numbers in cells in excel

How do I remove the delimiter? How are you typing the vertical line character?

"Spiky" wrote:

I don't think Vito is coming back after 3 years.

How about:
=SUBSTITUTE(A1,"|","New Text")

Or if you are trying to spread the data to various columns, splitting
it up at the vertical line, just use Text To Columns, Delimited, with
| as the delimiter.

  #7   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 622
Default how to limit amount of numbers in cells in excel

On Aug 20, 8:13 am, Challenger
wrote:
How do I remove the delimiter? How are you typing the vertical line character?



If you use Text to Columns, whatever delimiter you use will vanish.
You can use more than one, if you need a space or comma to separate
some columns, too. Otherwise, the formula I wrote before will get rid
of it and put in whatever text you want.

It's simple to type, surprisingly, but sorta hidden. The bar is
usually right above the Return/Enter button on a USA keyboard. It is
pictured as a broken line, but Windows actually types the solid line.
aka SHIFT-backslash.
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
Excel: match two cells in one sheet to two cells in another and return a third cells value Spence Excel Worksheet Functions 3 February 13th 11 06:33 AM
TRYING TO SET UP EXCEL SPREADSHEET ON MY COMPUTER MEGTOM New Users to Excel 5 October 27th 05 03:06 AM
Cells User Select Locked after upgrade to Excel 2002 TWilson Excel Discussion (Misc queries) 1 August 5th 05 12:22 PM
How to alter data on HTML webpage into Excell cells as numbers? roameri New Users to Excel 6 July 31st 05 11:42 PM
changing the way Excel displays selected cells P Boric Excel Discussion (Misc queries) 1 July 28th 05 01:09 PM


All times are GMT +1. The time now is 11:21 PM.

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"