Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Thread: Separate a string
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Oct 2010
    Posts
    132
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Separate a string

    Hello,

    I need help separating a string in cell AI. The string is:

    X0000357_1212011_USD_1359376.33 which is concatenated DealID "X0000357, Date "12/1/2011" Currency "USD" and Amount "1359376.33. As you can see they are separated by underscore _. In B1 I need a formula to extract the Deal ID X0000357; in C1 I need a formula to extract the Date 1212011 and format it 01-dec-2011; in D1 I need a formula to extract currency USD and E1 a formula to extract the amount 1359376.33.

    Please help. Thank you.

  2. #2
    Legend NateO's Avatar
    Join Date
    Feb 2002
    Location
    Minneapolis, Mn, USA
    Posts
    9,700
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Separate a string


  3. #3
    Board Regular
    Join Date
    Oct 2010
    Posts
    132
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Separate a string

    Thanks, that works. But what would be the formulas as I described? It will be much helpful to have a formula> Thanks.

  4. #4
    MrExcel MVP Rick Rothstein's Avatar
    Join Date
    Apr 2011
    Location
    New Jersey, USA
    Posts
    35,244
    Post Thanks / Like
    Mentioned
    92 Post(s)
    Tagged
    33 Thread(s)

    Default Re: Separate a string

    Try these...

    Code:
    B1:  =LEFT(A1,FIND("_",A1&"_")-1)
     
    C1:  =--TEXT(TRIM(MID(SUBSTITUTE(A1,"_",REPT(" ",LEN(A1))),LEN(A1)*(1),LEN(A1))),"0-00-0000")
     
    D1:  =TRIM(MID(SUBSTITUTE(A1,"_",REPT(" ",LEN(A1))),LEN(A1)*(2),LEN(A1)))
     
    E1:  =TRIM(MID(SUBSTITUTE(A1,"_",REPT(" ",LEN(A1))),LEN(A1)*(2),LEN(A1)))
    You will need to format the cells in Column C using this Custom Format pattern.... dd-mmm-yyyy

  5. #5
    Board Regular
    Join Date
    Oct 2010
    Posts
    132
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Separate a string

    Hi your E1 formula is identical to D1 and therefore returns the same value as D1. I need the formula to return the amount.

  6. #6
    Board Regular
    Join Date
    Oct 2010
    Posts
    132
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Separate a string

    Hi there any comments to my last question?

  7. #7
    Legend NateO's Avatar
    Join Date
    Feb 2002
    Location
    Minneapolis, Mn, USA
    Posts
    9,700
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Separate a string

    Notice a trending change in the functions? Try changing E1 to multiply by 3 instead of 2.

    Still, why not use Text to Columns? It's vastly more efficient, and you won't have to worry about syntax that might not be transparent to you...

  8. #8
    Legend NateO's Avatar
    Join Date
    Feb 2002
    Location
    Minneapolis, Mn, USA
    Posts
    9,700
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Separate a string

    You might end up with a problem in C1, too. Try this:

    =DATEVALUE(TEXT(TRIM(MID(SUBSTITUTE(A1,"_",REPT(" ",LEN(A1))),LEN(A1)*1,LEN(A1))),"0-0-0000"))

  9. #9
    Board Regular
    Join Date
    Oct 2010
    Posts
    132
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Separate a string

    Perfect!. Thank you

  10. #10
    MrExcel MVP Rick Rothstein's Avatar
    Join Date
    Apr 2011
    Location
    New Jersey, USA
    Posts
    35,244
    Post Thanks / Like
    Mentioned
    92 Post(s)
    Tagged
    33 Thread(s)

    Default Re: Separate a string

    Quote Originally Posted by NateO View Post
    You might end up with a problem in C1, too. Try this:

    =DATEVALUE(TEXT(TRIM(MID(SUBSTITUTE(A1,"_",REPT(" ",LEN(A1))),LEN(A1)*1,LEN(A1))),"0-0-0000"))
    I missed the December 1st date, but I don't think your formula is the answer as it will fail for a date like 11222011 (that is 11/22/2011). The problem is the OP's decision to recognize 1212011 as December 1, 20011 instead of January 21, 2011. The only way I can see that being a valid decision is if the dates are always the first of the month, but we have not been told that is the case.

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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