+ Reply to Thread
Results 1 to 3 of 3

Selecting text from the middle of a string

  1. #1
    Registered User
    Join Date
    02-25-2016
    Location
    USA
    MS-Off Ver
    2013
    Posts
    5

    Question Selecting text from the middle of a string

    I have a list of strings in a column and am trying to separate out a piece of each of those strings.

    However, each string has a different number of characters and words with the piece that I'm trying to identify buried in the middle.

    For example, "Fruit Mix 6/20z", is one of these strings. I am trying to pull the "6" into a new column and the "20" into another new column.

    "Brussel Sprt Seasnd 12/10z TJs" is another example. In this instance I want to pull the "12" into a column and the "10" into another.

    In all these strings, the numbers I want to pull out are always immediately before a "/" and immediately after.

    Thanks for all your help!

  2. #2
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,903

    Re: Selecting text from the middle of a string

    Try this
    Enter formula in B1, copy across and down
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    v A B C
    1 Fruit Mix 6/20z 6 20
    2 Brussel Sprt Seasnd 12/10z TJs 12 10
    If you like my answer please click on * Add Reputation
    Don't forget to mark threads as "Solved" if your problem has been resolved

    "Nothing is so firmly believed as what we least know."
    --Michel de Montaigne

  3. #3
    Forum Guru
    Join Date
    02-27-2016
    Location
    Vietnam
    MS-Off Ver
    2016
    Posts
    5,912

    Re: Selecting text from the middle of a string

    Try this formula in B1:

    =LOOKUP(10^10,--MID(A1,FIND("/",A1)-{1,2,3,4,5,6,7},{1,2,3,4,5,6,7}))

    In C1:

    =LOOKUP(10^10,--MID(A1,FIND("/",A1)+1,{1,2,3,4,5,6,7}))

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Pick out text from middle of a string
    By aaron.phelan12345 in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 11-06-2015, 01:00 PM
  2. Sort by text in the middle of a string
    By javeds in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 01-08-2014, 12:32 PM
  3. [SOLVED] How to remove dot(s) .. or ... in front of text string, not in a middle
    By tuongtu3 in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 09-23-2013, 05:45 PM
  4. [SOLVED] if formula looking at value in middle of text string
    By SAsplin in forum Excel Formulas & Functions
    Replies: 13
    Last Post: 04-25-2013, 06:57 AM
  5. [SOLVED] pulling text from the middle of a string
    By DRFILL in forum Excel General
    Replies: 3
    Last Post: 09-21-2012, 11:35 AM
  6. Extract text from the middle of a string for concatenation
    By christopherp in forum Excel General
    Replies: 1
    Last Post: 05-16-2011, 08:55 AM
  7. delete middle part of text string
    By maacmaac in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 03-30-2009, 03:59 PM

Tags for this Thread

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