+ Reply to Thread
Results 1 to 2 of 2

Extract text from the middle of a string for concatenation

  1. #1
    Registered User
    Join Date
    12-23-2003
    Location
    Perth, Western Australia
    Posts
    27

    Extract text from the middle of a string for concatenation

    Hi there

    I have several cells in a sheet from which I would would like to extract text from the middle of the string and concat the word actual on the end. e.g

    b3 contains:

    00 PRELIMINARIES - FORECAST

    In the cell below that string, I would like to extract the word "preliminaries" and add the word "actual" to the end of it (result = "Preliminaries actual" (in sentence case is possible))

    b5 contains:

    15 STRUCTURAL STEEL - FORECAST

    In the cell below I would like to extract the words "Structural steel" and add the word "actual" to the end of it (result = "Structural steel actual" (in sentence case is possible))

    Some of the strings have two words I would like to extract and some only have a single word - if somenone could assist with a formula that would work in both cases it would be great.

    Thanks in advance

  2. #2
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,582

    Re: Extract text from the middle of a string for concatenation

    We need to find a pattern which Excel can look at. Are all the extracted words preceeded by a number and followed by a dash? If not, can you give a range of examples?

    Based on your two examples
    =MID(A1,FIND(" ",A1)+1,FIND("-",A1,FIND(" ",A1))-5) & " Actual"
    Last edited by ChemistB; 05-16-2011 at 10:58 AM.
    ChemistB
    My 2?

    substitute commas with semi-colons if your region settings requires
    Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
    If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)

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

+ 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