Question about separating numbers and letters in Excel

Factual Questions
1

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.

2

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

3

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.

4

You’re absolutely right.

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

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

5

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