Question about separating numbers and letters in Excel

[Linty Fresh]

I'm kind of a n00b when it comes to using Excel functions, and I've run into the following situation:

I'm working with addresses in a field, specifically street numbers as follows:

Street Number
127A
2B

The column is in the text format.

I want to create another column containing just the number portion of the Street Number column without the letter. Is there any formula I can use to determine the length of numerals before each letter, or do I have to separate them manually?

Many thanks.

[Szlater]

If there is only one letter per cell and it's at the end of the number you should be able to do the following.

To produce a column of just the numbers use:

=LEFT(XX,(LEN(XX))-1)

Where XX = the cell containing the original data

To produce a column of just the letters use:

=RIGHT(XX,1)

Where XX = the cell containing the original data

[CookingWithGas]

Originally Posted by Szlater

If there is only one letter per cell and it's at the end of the number you should be able to do the following.

That works as long as there is always a letter at the end.

If there is either no letter, or only one letter, then the following will work (I have tested this):

=IF(AND(RIGHT(A1,1)>="a",RIGHT(A1,1)<"Z"), LEFT(A1,LEN(A1)-1),A1)

However, be aware that addresses are notoriously irregular and the minute you use this formula you will find an address that breaks your assumptions. There are companies who do a big business in just canonizing and validating addresses.

[Szlater]

Originally Posted by CookingWithGas

That works as long as there is always a letter at the end.

If there is either no letter, or only one letter, then the following will work (I have tested this):

=IF(AND(RIGHT(A1,1)>="a",RIGHT(A1,1)<"Z"), LEFT(A1,LEN(A1)-1),A1)

You're absolutely right.

But you could also wrap your formula with VALUE() to format it nicely</weakattemptatsavingface>

=VALUE(IF(AND(RIGHT(A1,1)>="a",RIGHT(A1,1)<"Z"), LEFT(A1,LEN(A1)-1),A1))

[Linty Fresh]

I just wanted to post back and thank everyone who helped. It really was a big help, so thanks!