Results 1 to 7 of 7
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Las Vegas, Nevada, USA
    Posts
    334
    Thanks
    8
    Thanked 0 Times in 0 Posts
    An application in our company extracts a report to Excel. The values extract cleanly, but the program concatenates the customer name and number into the same cell. The account number appears after the customer name and is surrounded with one set of parenthesis. Most of the time a customer has only one account. But once in a while a customer's name is repeated (more than one account) so the program adds a numer in parenthesis in front of the account number (already in parenthesis).

    Like this:
    This Guy (501502)
    That Guy (413109)
    That Guy(1) (321158)

    I'm OK getting This Guy's account with =LEFT(RIGHT(F2,FIND(")",F2,1)-FIND("(",F2,1)),FIND(")",F2,1)-FIND("(",F2,1)-1)
    and I've no doubt that there may be a simpler way to do this.

    But the same formula for That Guy(1) returns an 8, since the formula's only looking for one set of parenthesis.

    Please show me how I can use just one formula to get the correct account for each customer.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    You could use the following monster formula:

    =MID(F2,IF(ISERROR(SEARCH("(",F2,SEARCH("(",F2)+1) ),SEARCH("(",F2),SEARCH("(",F2,SEARCH("(",F2)+1))+ 1,SEARCH(")",F2,IF(ISERROR(SEARCH("(",F2,SEARCH("( ",F2)+1)),SEARCH("(",F2),SEARCH("(",F2,SEARCH("(", F2)+1))+1)-IF(ISERROR(SEARCH("(",F2,SEARCH("(",F2)+1)),SEARCH ("(",F2),SEARCH("(",F2,SEARCH("(",F2)+1))-1)

    But I'd prefer to use intermediate formulas as in the attached workbook.

    It would be even better to demand that the application returns the customer name and account number in separate cells.
    Attached Files Attached Files

  3. #3
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Las Vegas, Nevada, USA
    Posts
    334
    Thanks
    8
    Thanked 0 Times in 0 Posts
    That's it. Works beautifully. And it is a monster. Thank you Hans.

  4. #4
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,054
    Thanks
    2
    Thanked 417 Times in 346 Posts
    [quote name='Arcturus16a' post='776499' date='23-May-2009 07:15']An application in our company extracts a report to Excel. The values extract cleanly, but the program concatenates the customer name and number into the same cell. The account number appears after the customer name and is surrounded with one set of parenthesis. Most of the time a customer has only one account. But once in a while a customer's name is repeated (more than one account) so the program adds a numer in parenthesis in front of the account number (already in parenthesis).

    Like this:
    This Guy (501502)
    That Guy (413109)
    That Guy(1) (321158)

    I'm OK getting This Guy's account with =LEFT(RIGHT(F2,FIND(")",F2,1)-FIND("(",F2,1)),FIND(")",F2,1)-FIND("(",F2,1)-1)
    and I've no doubt that there may be a simpler way to do this.

    But the same formula for That Guy(1) returns an 8, since the formula's only looking for one set of parenthesis.

    Please show me how I can use just one formula to get the correct account for each customer.[/quote]
    Hi Arcturus,

    How about:
    =SUBSTITUTE(RIGHT(A1,LEN(A1)-FIND(" (",A1)-1),")","")
    This assumes there's no spaces between 'That Guy' and '(1)'.
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    [quote name='macropod' post='776513' date='23-May-2009 00:07']Hi Arcturus,

    How about:
    =SUBSTITUTE(RIGHT(A1,LEN(A1)-FIND(" (",A1)-1),")","")
    This assumes there's no spaces between 'That Guy' and '(1)'.[/quote]
    If there will never be a space between the name and the opening parenthesis in (1) etc., that is certainly a lot easier!

  6. #6
    2 Star Lounger
    Join Date
    Aug 2004
    Posts
    123
    Thanks
    0
    Thanked 1 Time in 1 Post
    [quote name='Arcturus16a' post='776499' date='22-May-2009 21:15']....................
    Like this:
    This Guy (501502)
    That Guy (413109)
    That Guy(1) (321158)................
    and I've no doubt that there may be a simpler way to do this.
    .......................[/quote]

    Try this formula, can work on like this :
    That Guy(1) (321158)
    That Guy (1) (321159)

    ="("&-LOOKUP(1,-RIGHT(SUBSTITUTE(F2,")",""),ROW($1:$255)))&")"

    Regards
    Bosco

  7. #7
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,054
    Thanks
    2
    Thanked 417 Times in 346 Posts
    [quote name='bosco_yip' post='776542' date='23-May-2009 11:22']Try this formula, can work on like this :
    That Guy(1) (321158)
    That Guy (1) (321159)

    ="("&-LOOKUP(1,-RIGHT(SUBSTITUTE(F2,")",""),ROW($1:$255)))&")"

    Regards
    Bosco[/quote]
    Hi Bosco,

    Even better!

    For Arcturus' purposes, which I believe don't require the brackets, this could be shortened to:
    =-LOOKUP(1,-RIGHT(SUBSTITUTE(F2,")",""),ROW($1:$255)))
    Cheers,

    Paul Edstein
    [MS MVP - Word]

Posting Permissions

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