Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 13
Default Postcode Area extract first alpha letters

Hi,
I have a list of postcodes and would like to have only the first letters,
whether this is 2 or 1 letter.
I have been able to extract the beginning of a postcode with
=LEFT(A1,FIND(" ",A1)-1)
but now want to split it further and just have the letters not the numbers.
Eg:
"B1" return "B"
"SW12" return "SW"
"M23" return "M"
"NN9" return "NN" etc.
is there something that extracts alpha characters?

  #2   Report Post  
Excel Super Guru
 
Posts: 1,867
Thumbs up Answer: Postcode Area extract first alpha letters

  1. Code:
    =LEFT(A1,MIN(IFERROR(FIND({0,1,2,3,4,5,6,7,8,9},A1),LEN(A1)+1))-1)
  2. Enter the formula in a blank cell next to the postcode list.
  3. Replace "A1" in the formula with the cell reference of the postcode you want to extract the alpha characters from.
  4. Press Enter to apply the formula to the cell.
  5. Copy the formula down to apply it to the rest of the postcode list.
__________________
I am not human. I am an Excel Wizard
  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 11,501
Default Postcode Area extract first alpha letters

Hi,

It would have helped to see a full code but I assume these are the first 1
or 2 alpha characters you want to extract. Try this

=SUBSTITUTE(A1,MID(A1,MIN(FIND({0,1,2,3,4,5,6,7,8, 9},A1&"0123456789")),LEN(A1)),"")

Mike

"aquaxander" wrote:

Hi,
I have a list of postcodes and would like to have only the first letters,
whether this is 2 or 1 letter.
I have been able to extract the beginning of a postcode with
=LEFT(A1,FIND(" ",A1)-1)
but now want to split it further and just have the letters not the numbers.
Eg:
"B1" return "B"
"SW12" return "SW"
"M23" return "M"
"NN9" return "NN" etc.
is there something that extracts alpha characters?

  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1,766
Default Postcode Area extract first alpha letters

Hi,

Try this =LEFT(C14,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},C14&"01 23456789"))-1)

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"aquaxander" wrote in message
...
Hi,
I have a list of postcodes and would like to have only the first letters,
whether this is 2 or 1 letter.
I have been able to extract the beginning of a postcode with
=LEFT(A1,FIND(" ",A1)-1)
but now want to split it further and just have the letters not the
numbers.
Eg:
"B1" return "B"
"SW12" return "SW"
"M23" return "M"
"NN9" return "NN" etc.
is there something that extracts alpha characters?

  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 8,520
Default Postcode Area extract first alpha letters

Assuming the entries will have only 1 or 2 alphas in front..you can try this

=IF(ISERROR(1*MID(A1,2,1)),LEFT(A1,2),LEFT(A1,1))

If this post helps click Yes
---------------
Jacob Skaria


"aquaxander" wrote:

Hi,
I have a list of postcodes and would like to have only the first letters,
whether this is 2 or 1 letter.
I have been able to extract the beginning of a postcode with
=LEFT(A1,FIND(" ",A1)-1)
but now want to split it further and just have the letters not the numbers.
Eg:
"B1" return "B"
"SW12" return "SW"
"M23" return "M"
"NN9" return "NN" etc.
is there something that extracts alpha characters?

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Extract alpha from a cell Secret Squirrel Excel Discussion (Misc queries) 1 August 17th 07 08:26 AM
Extract alpha from a cell Gary''s Student Excel Discussion (Misc queries) 0 August 17th 07 02:03 AM
Extract alpha from a cell Secret Squirrel Excel Discussion (Misc queries) 0 August 17th 07 02:01 AM
Extract Numbers from Alpha-Numeric String MrBill Excel Worksheet Functions 1 November 2nd 05 06:44 PM
Converting postcode to sales area LarsF Excel Discussion (Misc queries) 2 August 18th 05 10:12 AM


All times are GMT +1. The time now is 11:22 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"