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 Formula

Discussion in 'Business Applications' started by Lhith, Nov 10, 2007.

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

    Lhith Thread Starter

    Joined:
    Nov 10, 2007
    Messages:
    3
    Alright, I got this formula from a post here listed in 2005, it does exactly what I need, but I need a slight change.
    I normally don't use excel, just doing up a sheet to sort some of my mom's clients for her.
    I need to sort these by address, but because of my address format it doesn't work correctly.

    What i have is:
    ID# | Name | Address | Phone | PS

    My address format is :
    14 DUCHESS AV
    15 DUCHESS AV
    33 FOURTH ST

    So it sorts them by #'s not streets, so i found this formula for a helper column:

    Formula is:
    =TRIM(MID(C2,SEARCH(" ",C2,1),LEN(C2)-SEARCH(" ",C2,1)+1))

    Only problem is, i have 200 or so rows of info to put in, and i really don't want to change the formula by hand for each row, I was looking for a wildcard to put in for the column field, but all i could find was info saying excel doesn't have wildcards :p

    I have a header row, so row one is taken, i need it to make a helper column for every C# from 2 down.

    :edit:
    Ok i realized when i drag the formula it auto updates the info for me :eek:
    But now i am having a new issue with it.

    Some of the info isn't sorting right still Ex:

     
  2. Lhith

    Lhith Thread Starter

    Joined:
    Nov 10, 2007
    Messages:
    3
    I'm guessing another trim formula to bring out the #'s as well then secondary sort by the #? But i really have no idea =-P
     
  3. The Villan

    The Villan

    Joined:
    Feb 20, 2006
    Messages:
    2,255
    Quickest way to split the numbers from the Name of the street is

    1. insert 2 columns to the right of column C
    2. Select all the data in column C
    3 Select the menu Data, text to Columns
    4 In the dialogue box select Fixed Width and click on Next
    5.Hopefully Excel should have created 2 columns - one being the street number, the other being the text after the number. If the text appears as 2 columns then drag the vertical black line to the right of the text.
    6 Click on Next
    7. Where it says Destination change that to $D$XX (XX being the first row number of the data)
    8. Click on the finish button.

    Your data should now be in two columns - Column D & E

    If you are happy with the result then you can delete column C

    If you are not happy with the result, close the file without saving. Re-open the existing file and try again.
     
  4. Lhith

    Lhith Thread Starter

    Joined:
    Nov 10, 2007
    Messages:
    3
    Hmm, just one thing, when you told me to move the black line, that step wasn't needed, because when i did it, it made the #'s and streets both go into D, but when i left the line where it defaulted to, it worked perfect.
    Thank you very much for the help!
     
  5. The Villan

    The Villan

    Joined:
    Feb 20, 2006
    Messages:
    2,255
    I wasn't sure what would happen there on your machine. Anyway it worked.
    Can you go to the top of the thread thread and click on the tools option and mark this as Solved please
     
  6. 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/650078

  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