Results 1 to 8 of 8
  1. #1
    2 Star Lounger
    Join Date
    Jul 2006
    Location
    Bangalore, India
    Posts
    180
    Thanks
    1
    Thanked 0 Times in 0 Posts
    HI Excel experts,

    I have a excel sheet with is an extract from one of our application. One cell has a partcular format of contents

    MANAGER: XXXXXXXX ID: 12345 COUNTRY: IND DIVISION: APEA BUSINESS SEGMENT: FUNCTION: XXXXXXXX This has been requested by ID: 12345 NAME: XXXXXXXX DIVISION: APEA BUSINESS SEGMENT: This is the content: ID: Name: JobTitle: Details of Change:

    I would like to either split the cell from the word starting "This is the content"
    OR
    remove all the text in the cell before the following word "This is the content"

    This is help me to a great extent. Looking forward to a quick and simple solution.

    Regards
    Baiju

  2. #2
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    Select the data, press Ctrl+H, and enter:

    Find: *This is the content

    Replace: This is the content




    then press Replace All.
    Regards,
    Rory

    Microsoft MVP - Excel

  3. #3
    2 Star Lounger
    Join Date
    Jul 2006
    Location
    Bangalore, India
    Posts
    180
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Hi Rory,

    That was pretty simple and effective thanks a ton.

    Is it also possible for me to extract only ID: 12345 to a seperate cell given that the text lenght varies and is seperated by a space?

    Regards
    Baiju

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    I don't understand the request. could you elaborate?

    Steve

  5. #5
    2 Star Lounger
    Join Date
    Jul 2006
    Location
    Bangalore, India
    Posts
    180
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Hi Rory

    with the find replace the following contents

    MANAGER: XXXXXXXX ID: 12345 COUNTRY: IND DIVISION: APEA BUSINESS SEGMENT: FUNCTION: XXXXXXXX This has been requested by ID: 12345 NAME: XXXXXXXX DIVISION: APEA BUSINESS SEGMENT: This is the content: ID: 12345 Name:xxxxxx JobTitle:xxxxxxx Details of Change: a;fd;sfj;ldsjf;lsa

    Becomes
    This is the content: ID: 12345 Name:xxxxxx JobTitle:xxxxxxx Details of Change: a;fd;sfj;ldsjf;lsa

    Now is it possible for have a formula in the adjacent cells for me to extract only
    ID: 12345
    Details of change :

    Since both do not have a specified length.

    Hope this clarifies.

    Thanks
    Baiju

  6. #6
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    It depends if the formatting of your data is exactly regular?
    Regards,
    Rory

    Microsoft MVP - Excel

  7. #7
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,820
    Thanks
    133
    Thanked 481 Times in 458 Posts
    Try this:

    If your data
    ID: 12345 Name:xxxxxx JobTitle:xxxxxxx Details of Change: a;fd;sfj;ldsjf;lsa
    ..is in cell say, [C6], then in adjacent cell [D6] put this formula:

    =LEFT(C6,10)&MID(C6,FIND("Details of Change:",C6,1),99)

    The Left(C6,10) gives you the beginning 10 characters (i.e. including the space after 12345):
    ID: 12345

    ..and the last bit
    ,99)
    ..returns up to 99 characters starting from the bit that begins
    Details of Change: a;fd;sfj;ldsjf;lsa

    zeddy

  8. #8
    WS Lounge VIP
    Join Date
    Dec 2009
    Location
    Earth
    Posts
    8,170
    Thanks
    47
    Thanked 980 Times in 910 Posts
    =TRIM(MID(C6,FIND("ID:",C6,1),FIND("COUNTRY",C6,1)-FIND("ID:",C6,1)))
    =RIGHT(C6,LEN(C6)-FIND("Details of Change",C6,1)+1)

    cheers, Paul

Posting Permissions

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