Results 1 to 12 of 12
  1. #1
    Star Lounger
    Join Date
    Jul 2005
    Location
    Jakarta, Indonesia
    Posts
    83
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi all,

    Kindly need some formula to separate text like below in a cell

    311011-Team Review Panel-PR Core Team

    in to three column below;

    column A = 311011
    column B = Team Review Panel
    column C = PR Core Team


    kind regards

    Indra

  2. #2
    3 Star Lounger
    Join Date
    May 2008
    Location
    India
    Posts
    306
    Thanks
    0
    Thanked 0 Times in 0 Posts
    You can use Text to Column from Data Menu

    [attachment=84781:x1.PNG]

    [attachment=84782:x2.PNG]
    Attached Images Attached Images
    Regards
    Prasad

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Psasad's suggestion of using Data | Text to Columns... is very good, but I'd choose Delimited in step 1, and tick Other and specify - as delimiter in step 2. You then only need to click Finish.

  4. #4
    Star Lounger
    Join Date
    Jul 2005
    Location
    Jakarta, Indonesia
    Posts
    83
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thanks Prasad,

    actually I was expecting some formula in next column, because this data will be received from various report to be consolidated in one working sheet.

    using text to column is considered as second option.


    regards

    Indra

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    I have attached a solution using formulas. I've left the intermediate formulas in the workbook; you could incorporate them into the formulas that return the left, middle and right parts but it would make them harder to read and to edit. You could hide the columns with intermediate formulas instead.
    Attached Files Attached Files

  6. #6
    3 Star Lounger
    Join Date
    May 2008
    Location
    India
    Posts
    306
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='785539' date='21-Jul-2009 12:43']I have attached a solution using formulas. I've left the intermediate formulas in the workbook; you could incorporate them into the formulas that return the left, middle and right parts but it would make them harder to read and to edit. You could hide the columns with intermediate formulas instead.[/quote]
    A new way to split values but somehow limited to make calculation as it will return all the values as text.
    Regards
    Prasad

  7. #7
    Star Lounger
    Join Date
    Jul 2005
    Location
    Jakarta, Indonesia
    Posts
    83
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hans,

    this is exactly what I need. intermediate formula is brilliant approach to teach guy like me!

    many thanks,

    indra

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    [quote name='prasad' post='785542' date='21-Jul-2009 13:57']A new way to split values but somehow limited to make calculation as it will return all the values as text.[/quote]
    In situations like this, it is often desirable to leave the result as text. But if necessary, one could use

    =VALUE(LEFT(A2,B2-1))

    for the left part to return a number value.

  9. #9
    3 Star Lounger
    Join Date
    May 2008
    Location
    India
    Posts
    306
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='785545' date='21-Jul-2009 13:03']In situations like this, it is often desirable to leave the result as text. But if necessary, one could use

    =VALUE(LEFT(A2,B2-1))

    for the left part to return a number value.[/quote]
    Got it. Thanks Hans to make me clear.
    Regards
    Prasad

  10. #10
    New Lounger
    Join Date
    Jun 2009
    Location
    India
    Posts
    17
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I would like to suggest for extracting Right most portion, without limiting to 100 cheractors:

    use
    =RIGHT(A2,LEN(A2)-C2)

    insted of
    =MID(A2,C2+1,100)

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    If the third part can be any length, it would indeed be better to use the RIGHT function instead of the MID function.

    If the length of the third part is limited, MID will work fine; it is possible of cours to specify a larger or smaller number than 100.

  12. #12
    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
    You could also use:
    =MID(A2,C2+1,len(A2))
    Regards,
    Rory

    Microsoft MVP - Excel

Posting Permissions

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