1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Solved: Excel 2003: Replacing Large Numbers with Smaller Numbers

Discussion in 'Business Applications' started by smooth, Dec 20, 2005.

Thread Status:
Not open for further replies.
Advertisement
  1. smooth

    smooth Thread Starter

    Joined:
    Sep 26, 2005
    Messages:
    4,029
    First Name:
    Garrett
    Hey everybody. :D

    I don't know if the title is a good one for this problem, but here goes.

    I have a column that has numbers in this format: 54321-1234

    Now, I want to take off the last 4 numbers, and the dash, from all the numbers in the column, using a formula. So that the output would be: 54321.

    I want it so the first 5 numbers stay the same, but the dash and last 4 are gone. Is this possible?

    Thanks.
     
  2. cristobal03

    cristobal03

    Joined:
    Aug 5, 2005
    Messages:
    3,086
    The column is text, correct? You can use the LEFT worksheet function.

    LEFT(string or range, length)

    So, if the range was A:A, and you wanted the output in column B, the formula for cell B1 would be

    =LEFT($A1, 5)

    Then you'd just do a drag-copy for the range you were interested in trimming.

    HTH

    chris.
     
  3. cristobal03

    cristobal03

    Joined:
    Aug 5, 2005
    Messages:
    3,086
    [bump]

    That won't change the value of the cells in column A. If that's what you're interested in doing, we'll have to try something else.

    chris.
     
  4. ProphetX

    ProphetX

    Joined:
    May 17, 2005
    Messages:
    28
    If all the numbers are the same after the hyphen, you could do a find and replace and replace the text with nothing. e.g. Find: -1234 Replace: [blank] Replace All.

    Alternatively, you can copy that column into Word, convert table to text, convert text to 2-column table using hyphens as cell dividers, copy column 1 back to excel.

    As for a macro in Excel to do this for you, I am not able to assist with that.

    Hope this helps.
     
  5. smooth

    smooth Thread Starter

    Joined:
    Sep 26, 2005
    Messages:
    4,029
    First Name:
    Garrett
    That worked great Chris. Thanks.

    How would I do this? I want the formula to work for all of Column B. How do I get the formula for that column to do that?
     
  6. Zack Barresse

    Zack Barresse

    Joined:
    Jul 25, 2004
    Messages:
    5,452
    Copy the new formula(s), press Alt + E, S, V, Enter. Either delete the original value(s) or copy the new one's over the old one's.

    Are these zip codes? Did you know there is a custom format for zip codes, with and without the last four ("-0000")?
     
  7. Zack Barresse

    Zack Barresse

    Joined:
    Jul 25, 2004
    Messages:
    5,452
    Oh yuck. Please no. Excel has a Text to Columns feature in the Data menu. ;)
     
  8. cristobal03

    cristobal03

    Joined:
    Aug 5, 2005
    Messages:
    3,086
    When you activate a cell, it's marked by a thicker border with a dragging target (or whatever it's called) in the lower right-hand corner. With that formula in cell B1, click the target in the lower right-hand corner of the cell and drag the selection down the column for the desired length.

    chris.
     

    Attached Files:

  9. smooth

    smooth Thread Starter

    Joined:
    Sep 26, 2005
    Messages:
    4,029
    First Name:
    Garrett
    Thanks Chris. :D That worked perfectly. The pictures really helped. lol

    Once again you have helped me through a problem. Thanks again. :D

    Thanks also to the other replies. :D

    firefytr, I couldn't get the Alt + E, S, V, Enter to work right. I must have been hitting the keys wrong or something. Was it a paste special?
     
  10. cristobal03

    cristobal03

    Joined:
    Aug 5, 2005
    Messages:
    3,086
    Yeah, that would paste the values rather than the formula. If you try that now, selecting the column with the new formula, then targetting a new, blank column, that will paste the values not the formula.

    In case you wanted to actually trim the original data.

    chris.
     
  11. Zack Barresse

    Zack Barresse

    Joined:
    Jul 25, 2004
    Messages:
    5,452
    Yes, it's Paste Special/Values.

    It's:

    Press (together) Alt + E
    Let go
    Press the S key
    Press the V key
    Press Enter

    All together:
    Alt + E, S, V

    Note the + sign and , signs; it's not all one combo. I don't know what I did before learning this combo. ;)
     
  12. smooth

    smooth Thread Starter

    Joined:
    Sep 26, 2005
    Messages:
    4,029
    First Name:
    Garrett
    Thanks. I'll have to keep that combo in mind. :D

    Thanks again to everyone for helping me out.
     
  13. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/426674

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice