Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Results 1 to 5 of 5

Thread: Extract Text From Alphanumeric String

  1. #1
    Join Date
    12th October 2006
    Location
    London
    Posts
    160

    Extract Text From Alphanumeric String

    Hi,
    I need to extract just the text from cells which contain both letters and numbers. There can be one or two letters, followed by one, two or three numbers. (For info, these are chemical elements and their masses).

    So for instance,a cell could contain anything from the following B9, B11, S32, Ca44, Mo100, I129, Th226, U238 etc etc.

    I know I've seen this done before, but I can't find it now.

    It also has to be formula based, not using VBA.

    Anyone remember how to do this please?

    Thanks

    Dave

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    18th September 2005
    Location
    Hampshire, UK
    Posts
    1,278

    Re: Extract Text From Alphanumeric String

    Hi Dave

    Here's one option:

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

    Richard

    EDIT: More correctly:

    =LEFT(A1,MIN(FIND({"0","1","2","3","4","5","6","7","8","9"},A1&"0123456789"))-1)
    Last edited by RichardSchollar; July 16th, 2007 at 18:45.

    Excel Video Tutorials / Excel Dashboards Reports


  3. #3
    Join Date
    24th January 2003
    Location
    Australia
    Posts
    31,708

  4. #4
    Join Date
    12th October 2006
    Location
    London
    Posts
    160

    Re: Extract Text From Alphanumeric String

    Thanks Richard, that's perfect.

    Thanks Dave, I'd found that UDF to extract numbers, and used it before, but this time I needed to extract the text.

    Many thanks to everyone here.

    Dave

    Excel Video Tutorials / Excel Dashboards Reports


  5. #5
    Join Date
    24th January 2003
    Location
    Australia
    Posts
    31,708

    Re: Extract Text From Alphanumeric String

    Sorry, my bad I assumed you wanted the numbers.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Possible Answers

  1. Extract Text From Alphanumeric Cell
    By Absa in forum EXCEL HELP
    Replies: 4
    Last Post: March 10th, 2008, 16:06
  2. Extract Numbers From Alphanumeric Text
    By muhes in forum EXCEL HELP
    Replies: 9
    Last Post: February 23rd, 2008, 07:50
  3. Extract Part Of Text String
    By suaiko9999 in forum EXCEL HELP
    Replies: 34
    Last Post: August 17th, 2007, 16:52
  4. Extract Text From String-vba
    By krishna.a in forum EXCEL HELP
    Replies: 2
    Last Post: May 2nd, 2007, 22:43
  5. Extract Text From a String
    By jonny in forum EXCEL HELP
    Replies: 8
    Last Post: October 4th, 2006, 19:36

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
porno