# Question about separating numbers and letters in Excel

**URL:** <https://boards.straightdope.com/t/question-about-separating-numbers-and-letters-in-excel/448822>\
**Category:** Factual Questions\
**Created:** [May 10, 2008, 5:12pm UTC](https://boards.straightdope.com/t/question-about-separating-numbers-and-letters-in-excel/448822 "2008-05-10T17:12:23Z")\
**Posts on this page:** 5\
**Page:** 1

<div class="post-metadata">

**Author:** ![Linty\_Fresh](https://avatars.discourse-cdn.com/v4/letter/l/c5a1d2/32.png) [@Linty\_Fresh](https://boards.straightdope.com/u/Linty_Fresh)\
**Post date:** [May 10, 2008, 5:12pm UTC](https://boards.straightdope.com/t/question-about-separating-numbers-and-letters-in-excel/448822/1 "2008-05-10T17:12:23Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![Szlater](https://avatars.discourse-cdn.com/v4/letter/s/90ced4/32.png) [@Szlater](https://boards.straightdope.com/u/Szlater)\
**Post date:** [May 10, 2008, 7:03pm UTC](https://boards.straightdope.com/t/question-about-separating-numbers-and-letters-in-excel/448822/2 "2008-05-10T19:03:14Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![CookingWithGas](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/cookingwithgas/32/485_2.png) [@CookingWithGas](https://boards.straightdope.com/u/CookingWithGas)\
**Post date:** [May 10, 2008, 7:12pm UTC](https://boards.straightdope.com/t/question-about-separating-numbers-and-letters-in-excel/448822/3 "2008-05-10T19:12:46Z")

</div>

[QUOTE=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.  
[/QUOTE]  
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.

---

<div class="post-metadata">

**Author:** ![Szlater](https://avatars.discourse-cdn.com/v4/letter/s/90ced4/32.png) [@Szlater](https://boards.straightdope.com/u/Szlater)\
**Post date:** [May 10, 2008, 7:22pm UTC](https://boards.straightdope.com/t/question-about-separating-numbers-and-letters-in-excel/448822/4 "2008-05-10T19:22:17Z")

</div>

[QUOTE=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)

[/QUOTE]

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))

---

<div class="post-metadata">

**Author:** ![Linty\_Fresh](https://avatars.discourse-cdn.com/v4/letter/l/c5a1d2/32.png) [@Linty\_Fresh](https://boards.straightdope.com/u/Linty_Fresh)\
**Post date:** [May 13, 2008, 12:05am UTC](https://boards.straightdope.com/t/question-about-separating-numbers-and-letters-in-excel/448822/5 "2008-05-13T00:05:57Z")

</div>

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