+ Reply to Thread
Results 1 to 4 of 4

Picking out text and numbers within a number string

  1. #1
    Registered User
    Join Date
    12-06-2012
    Location
    London, England
    MS-Off Ver
    Excel 2007
    Posts
    2

    Picking out text and numbers within a number string

    Hello, I'm quite new to advance Excel formulas. I have a spreadsheet with a ton of style codes, for example - 073184CNT2.
    What I need is (for this one specifically) is the last 4 digits, starting with the letter. So CNT2. I need that data in a seperate column.
    It's not always 4 digits, but it does always start with a letter.

    Can anyone help me as to what formula I should be using? I've used a lot of =Right and substitutions but it seems to be grabbing just the C.

    Any help would be greatly appreciated.

    James

  2. #2
    Forum Guru tigeravatar's Avatar
    Join Date
    03-25-2011
    Location
    Colorado, USA
    MS-Off Ver
    Excel 2003 - 2013
    Posts
    5,361

    Re: Picking out text and numbers within a number string

    James,

    Welcome to the forum!
    Here's one way...
    Please Login or Register  to view this content.
    Hope that helps,
    ~tigeravatar

    Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble

  3. #3
    Forum Moderator Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: Picking out text and numbers within a number string

    Try this Array formula(Confirm using Control+Shift+Enter)-Not just enter.

    =TRIM(MID(LEFT(A1,LEN(A1)-COUNT(1*RIGHT(A1,COLUMN(1:1)))),MATCH(0,ISNUMBER(MID(A1,COLUMN(1:1),1)*1)*1,0),2^15))
    Regards

    Fotis.

    -This is my Greek whisper to Europe.

    --Remember, saying thanks only takes a second or two. Click the little star * below, to give some Rep if you think an answer deserves it.

    Advanced Excel Techniques: http://excelxor.com/

    --KISS(Keep it simple Stupid)

    --Bring them back.

    ---See about Acropolis of Athens.

    --Visit Greece.

  4. #4
    Registered User
    Join Date
    12-06-2012
    Location
    London, England
    MS-Off Ver
    Excel 2007
    Posts
    2

    Re: Picking out text and numbers within a number string

    That's perfect, thank you for replying so fast! That's great, I've been stuck on this for the past hour.
    Thanks guys!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1