+ Reply to Thread
Results 1 to 10 of 10

Trim values based on two conditions

Hybrid View

  1. 08-06-2013, 10:57 AM #1
    igoodable
    igoodable is offline
    Forum Contributor
    Join Date
    07-16-2013
    Location
    Houston, Texas
    MS-Off Ver
    Excel 2013
    Posts
    176

    Trim values based on two conditions

    Greeting experts!

    I need to trim all values before the comma of the first # signs.

    DATA: (note there are sometimes a space between the # symbol)
    106743 GALS SW, 124690 GALS L GEL, 211018 GALS XL GEL, 90340 # 100 MESH WHITE SD, 297639# 30/50 WHITE SD
    106342 GALS SW, 115352 GALS L GEL, 204822 GALS XL GEL, 50180# 100 MESH WHITE SD, 301684 # 40/70 WHITE SD
    107284 GALS SW, 117447 GALS L GEL, 200158 GALS XL GEL, 30000 # 100 MESH WHITE SD, 150000# 30/50 WHITE SD

    Results:
    90340 # 100 MESH WHITE SD, 297639# 30/50 WHITE SD
    50180# 100 MESH WHITE SD, 301684 # 40/70 WHITE SD
    30000 # 100 MESH WHITE SD, 150000# 30/50 WHITE SD

    Thanks in advance
    Reply With QuoteRegister To Reply
  2. 08-06-2013, 11:06 AM #2
    popipipo
    popipipo is offline
    Forum Expert
    Join Date
    12-11-2011
    Location
    Netherlands
    MS-Off Ver
    office 365
    Posts
    3,656

    Re: Trim values based on two conditions

    =RIGHT(A1,49)
    =MID(A1,FIND("#",A1,1)-6,50)
    Try one of these
    Willem
    English is not my native language sorry for errors
    Please correct me if I'm completely wrong
    Reply With QuoteRegister To Reply
  3. 08-06-2013, 11:08 AM #3
    Jakobshavn
    Jakobshavn is offline
    Forum Expert Jakobshavn's Avatar
    Join Date
    08-17-2012
    Location
    Lakehurst, NJ, USA
    MS-Off Ver
    Excel 2007
    Posts
    1,970

    Re: Trim values based on two conditions

    Because the "," is always in the same location in your sample data:

    =MID(A1,56,9999)
    Gary's Student
    Reply With QuoteRegister To Reply
  4. 08-06-2013, 11:17 AM #4
    igoodable
    igoodable is offline
    Forum Contributor
    Join Date
    07-16-2013
    Location
    Houston, Texas
    MS-Off Ver
    Excel 2013
    Posts
    176

    Re: Trim values based on two conditions

    Thanks guys, the comma is not always at the same location and the number of digits is not always 6. ie: i could have something like this:

    124690 gw, 18 GEL, 340 # 100 MESH WHITE SD
    124690 gw, 18 GEL, 4050000# 100 MESH WHITE SD
    124690 gw, 18 GEL, 105000# 100 MESH WHITE SD
    Reply With QuoteRegister To Reply
  5. 08-06-2013, 11:25 AM #5
    L-Drr
    L-Drr is offline
    Forum Contributor
    Join Date
    08-02-2013
    Location
    Wageningen, The Netherlands
    MS-Off Ver
    365
    Posts
    495

    Re: Trim values based on two conditions

    If there is a formula to rewrite text backwards (I don't know if there is), you could use that and then look for the first comma after the position of the # and then use the backwards-formula again.
    Does anybody know if there is such a formula?
    When I say semicolon, u say comma!
    Reply With QuoteRegister To Reply
  6. 08-06-2013, 11:40 AM #6
    XOR LX
    XOR LX is offline
    Forum Expert XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,742

    Re: Trim values based on two conditions

    Hi,

    Try this array formula:

    =TRIM(MID(A1,LOOKUP(FIND("#",A1),FIND("|",SUBSTITUTE(A1,",","|",ROW(INDIRECT("1:"&LEN(A1)-LEN(SUBSTITUTE(A1,",","")))))))+1,255))

    Regards
    Click * below if this answer helped

    Advanced Excel Techniques: http://excelxor.com/
    Reply With QuoteRegister To Reply
  7. 08-06-2013, 12:36 PM #7
    tigeravatar
    tigeravatar is offline
    Forum Expert tigeravatar's Avatar
    Join Date
    03-25-2011
    Location
    Colorado, USA
    MS-Off Ver
    Excel 2003 - 2013
    Posts
    5,361

    Re: Trim values based on two conditions

    @XOR LX, Great formula! Very clever use of lookup As a side note, that formula does not need to be array-entered in order to return the correct results, and because Lookup ignores errors, you can shorten that a bit to:
    =TRIM(MID(A1,LOOKUP(FIND("#",A1),FIND("|",SUBSTITUTE(A1,",","|",ROW($1:$9))))+1,255))
    Hope that helps,
    ~tigeravatar

    Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble
    Reply With QuoteRegister To Reply
  8. 08-06-2013, 04:42 PM #8
    igoodable
    igoodable is offline
    Forum Contributor
    Join Date
    07-16-2013
    Location
    Houston, Texas
    MS-Off Ver
    Excel 2013
    Posts
    176

    Re: Trim values based on two conditions

    Could you explain the logic behind the lookup portion please?
    Reply With QuoteRegister To Reply
  9. 08-06-2013, 03:07 PM #9
    XOR LX
    XOR LX is offline
    Forum Expert XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,742

    Re: Trim values based on two conditions

    Good stuff, tigeravatar! Always have been guilty of overuse of array formulas!

    Cheers!
    Reply With QuoteRegister To Reply
  10. 08-06-2013, 03:21 PM #10
    igoodable
    igoodable is offline
    Forum Contributor
    Join Date
    07-16-2013
    Location
    Houston, Texas
    MS-Off Ver
    Excel 2013
    Posts
    176

    Re: Trim values based on two conditions

    Thanks you all. Very creative formulas indeed. I'm taking the vba approach, but I'll keep this formula in my arsenal.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. HELP !!How to add values to sheets based on two conditions
    By Abo Rawad in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 11-18-2012, 06:37 PM
  2. [SOLVED] Trying to sum up values based on 2 conditions
    By slacknoise in forum Excel General
    Replies: 3
    Last Post: 03-29-2012, 08:29 AM
  3. Summing values based on conditions
    By juliorevka in forum Excel General
    Replies: 1
    Last Post: 02-16-2012, 04:16 PM
  4. Entering values based on 3 conditions
    By jkarthi22 in forum Excel General
    Replies: 6
    Last Post: 07-29-2011, 02:12 AM
  5. selecting Values based on conditions
    By Chimero in forum Excel General
    Replies: 2
    Last Post: 07-19-2009, 04:04 AM

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1