+ Reply to Thread
Results 1 to 6 of 6

Extract multiple specific length sub strings from cells and place into adjacent column

  1. #1
    Registered User
    Join Date
    07-17-2019
    Location
    London, England
    MS-Off Ver
    2010
    Posts
    27

    Extract multiple specific length sub strings from cells and place into adjacent column

    Hello all!

    I have a rather large task to automate:

    Column A has cells that have task numbers and titles separated by paragraphs, i.e. cell A1 contains the following:

    1. FTTQ 20199 - Title xyz
    2. FTPQ 16402 - Title 001

    The titles could be anything including having multiple alphanumeric strings, and there may be up to 10 tasks per cell. The prefix is normally FT?Q where ? could be anything and bears no relation to the information I need to extract. All pertinent numeric strings are 5 digits long

    I need to break these down to the task numbers only listed in column B such that B1 is 20199, B2 is 16402 using the example above.

    I have attached a small sanitised sample spreadsheet that shows roughly the information I have and the information I want to extract.

    Cheers! Pete
    Attached Files Attached Files

  2. #2
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    Office 365 ProPlus
    Posts
    5,883

    Re: Extract multiple specific length sub strings from cells and place into adjacent column

    Try something like...
    Please Login or Register  to view this content.
    ?Progress isn't made by early risers. It's made by lazy men trying to find easier ways to do something.?
    ― Robert A. Heinlein

  3. #3
    Registered User
    Join Date
    07-17-2019
    Location
    London, England
    MS-Off Ver
    2010
    Posts
    27

    Re: Extract multiple specific length sub strings from cells and place into adjacent column

    Unfortunately sometimes the title also contains hyphens! Is there a way to bracket the string by "Q " and " -"?

    Does that make sense?

  4. #4
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    Office 365 ProPlus
    Posts
    5,883

    Re: Extract multiple specific length sub strings from cells and place into adjacent column

    It may be easier to do pattern matching then. You can use either RegEx or split by single space " " and use like operator for comparison.

    Assuming that numeric string of 5 char length (bounded by space) does not appear in title.
    Ex:
    Please Login or Register  to view this content.

  5. #5
    Registered User
    Join Date
    07-17-2019
    Location
    London, England
    MS-Off Ver
    2010
    Posts
    27

    Re: Extract multiple specific length sub strings from cells and place into adjacent column

    Works very well - there is a small amount of validation to do on the data but reduced an afternoons work to half an hour. Many thanks!

  6. #6
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    Office 365 ProPlus
    Posts
    5,883

    Re: Extract multiple specific length sub strings from cells and place into adjacent column

    You are welcome and thanks for the rep

    If you are satisfied with the answer provided, please mark thread as solved by using thread tool found at top of your initial post.

+ 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. [SOLVED] Excel VBA - Extract multiple strings from single string seperated by specific character
    By ironfelix717 in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 01-20-2019, 02:17 PM
  2. I need to extract the last part of a specfic strings and move this into specific column
    By Ciaran0612 in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 11-30-2015, 02:34 PM
  3. Replies: 9
    Last Post: 03-09-2015, 10:15 PM
  4. Extract data from one column & place in adjacent column
    By Heterodoxy in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 09-27-2014, 12:50 PM
  5. Replies: 4
    Last Post: 01-13-2014, 08:13 AM
  6. Replies: 3
    Last Post: 02-25-2013, 08:11 AM
  7. [SOLVED] Extract Code from Cell and Place it Adjacent one.
    By Patish in forum Excel General
    Replies: 10
    Last Post: 06-23-2012, 04:43 AM

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