Straight Dope Message Board > Main Question about separating numbers and letters in Excel
 Register FAQ Calendar Mark Forums Read

#1
05-10-2008, 01:12 PM
 Linty Fresh Guest Join Date: Feb 2003
Question about separating numbers and letters in Excel

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.

Last edited by Linty Fresh; 05-10-2008 at 01:13 PM.
#2
05-10-2008, 03:03 PM
 Szlater Guest Join Date: Feb 2006
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

Last edited by Szlater; 05-10-2008 at 03:04 PM.
#3
05-10-2008, 03:12 PM
 CookingWithGas Charter Member Join Date: Mar 1999 Location: Tysons Corner VA Posts: 9,322
Quote:
 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.
#4
05-10-2008, 03:22 PM
 Szlater Guest Join Date: Feb 2006
Quote:
 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))
#5
05-12-2008, 08:05 PM
 Linty Fresh Guest Join Date: Feb 2003
I just wanted to post back and thank everyone who helped. It really was a big help, so thanks!

 Bookmarks

 Thread Tools Display Modes Linear Mode

 Posting Rules You may not post new threads You may not post replies You may not post attachments You may not edit your posts BB code is On Smilies are On [IMG] code is Off HTML code is Off
 Forum Jump User Control Panel Private Messages Subscriptions Who's Online Search Forums Forums Home Main     About This Message Board     Comments on Cecil's Columns/Staff Reports     Straight Dope Chicago     General Questions     Great Debates     Elections     Cafe Society     The Game Room     In My Humble Opinion (IMHO)     Mundane Pointless Stuff I Must Share (MPSIMS)     Marketplace     The BBQ Pit Side Conversations     The Barn House

All times are GMT -5. The time now is 05:24 AM.