[SOLVED] Excel formula to use numbers and first space as delimiter

Excel formula to use numbers and first space as delimiter

I have written a Crystal Report for a user who exports the report as a "Microsoft Excel (97-2003) Data-Only" file. One of the columns is an address that is in the following format:

123 Main Street

456 S 3rd St

789 N W Lane

There are always a series of numbers followed by a single space and then the rest of the address. Is there an Excel formula that I can write against this column that would remove the first numbers and the first space - so that I can end up with the following, in this case:

Main Street

S 3rd St

N W Lane

Solution

There is probably more than a handful of ways to do what you want. One way would be to find the location of the first space, then take all of the characters to the right of it. For instance:

=RIGHT(A1,LEN(A1)-FIND(" ",A1))

Worked on your sample data. Depending on the quality of your data, this may be quite brittle and you should wrap it in error checking formulas as appropriate.