# Solved: Excel 2003 Formula

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

Not open for further replies.

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

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

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
But now i am having a new issue with it.

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

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

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.

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

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

As Seen On