+ Reply to Thread
Results 1 to 7 of 7

modified MID or REPLACE formula

  1. 07-06-2011, 04:33 PM #1
    drewz5
    drewz5 is offline
    Registered User
    Join Date
    07-06-2011
    Location
    California
    MS-Off Ver
    Excel 2010
    Posts
    4

    modified MID or REPLACE formula

    Hello, I'm new to this site, but I'm hoping that I can get some help with a formula I'm trying to create...
    In one cell (R2) I have this: COLOR=Black|SIZE=8| (this goes with a number of different products so the Color value and Size value vary)
    what I am trying to do is figure out a formula that will return to me in another cell (M1) the specific color that is in the first cell. I can't use a mid formula because the color isn't always Black and the Size can be anywhere from 8 to Medium so that often changes as well.
    Currently I'm using this formula =LEFT(R2;FIND("|";R2)-1), but afterwards I have to go in and find and replace "COLOR=" because that stays there. What I want to do is make it so the COLOR= never shows up.

    Also, I'm trying to figure out another formula that returns me the Size value of a product. Currently, I use the formula =RIGHT(R2;LEN(R2)-FIND("E=";R2)-1), but that also leaves a "|" at the end. If there was a way to get rid of this it would also help out a lot.

    Any help/tips/pointers are greatly appreciated, I don't know a whole lot about what formulas exist for Excel so that's why I'm here.
    Cheers
    Last edited by drewz5; 07-07-2011 at 02:32 PM.
    Reply With QuoteRegister To Reply
  2. 07-07-2011, 02:50 PM #2
    NBVC
    NBVC is offline
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: modified MID or REPLACE formula

    Try perhaps:

    =TRIM(MID(R2;FIND("=";R2)+1;FIND("|";R2)-FIND("=";R2)-1))

    and

    =SUBSTITUTE(MID(R2;FIND("SIZE=";R2)+5;255);"|";"")+0
    Where there is a will there are many ways.

    If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner

    Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
    Reply With QuoteRegister To Reply
  3. 07-07-2011, 03:01 PM #3
    drewz5
    drewz5 is offline
    Registered User
    Join Date
    07-06-2011
    Location
    California
    MS-Off Ver
    Excel 2010
    Posts
    4

    Re: modified MID or REPLACE formula

    Quote Originally Posted by NBVC View Post
    Try perhaps:

    =TRIM(MID(R2;FIND("=";R2)+1;FIND("|";R2)-FIND("=";R2)-1))

    and

    =SUBSTITUTE(MID(R2;FIND("SIZE=";R2)+5;255);"|";"")+0
    Trim formula works perfect! thanks! just curious, sometimes there are products that read like this: "Navy/Dark Blue" and I am supposed to change them to have spaces around the slash mark so it would be "Navy / Dark Blue"...would there be any way to include this in the Trim formula or is that something I'll have to Find and Replace?

    The Substitute formula seems to work for whole sizes (8, 9, 10), but is not working for half sizes (8.5, 9.5, etc) or for alphabetical (i.e. S, M, L, XL). I'm not sure if I had specified that that was something that was changing as well.

    Thanks for the help though!
    Reply With QuoteRegister To Reply
  4. 07-07-2011, 03:16 PM #4
    NBVC
    NBVC is offline
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: modified MID or REPLACE formula

    Try these:

    =SUBSTITUTE(TRIM(MID(R2;FIND("=";R2)+1;FIND("|";R2)-FIND("=";R2)-1));"/";" / ")

    =SUBSTITUTE(MID(R2;FIND("SIZE=";R2)+5;255);"|";"")
    Reply With QuoteRegister To Reply
  5. 07-07-2011, 03:34 PM #5
    drewz5
    drewz5 is offline
    Registered User
    Join Date
    07-06-2011
    Location
    California
    MS-Off Ver
    Excel 2010
    Posts
    4

    Re: modified MID or REPLACE formula

    Quote Originally Posted by NBVC View Post
    Try these:

    =SUBSTITUTE(TRIM(MID(R2;FIND("=";R2)+1;FIND("|";R2)-FIND("=";R2)-1));"/";" / ")

    =SUBSTITUTE(MID(R2;FIND("SIZE=";R2)+5;255);"|";"")
    Works great!! Thank you so much! Alright, one last challenge, and if this one isn't possible I totally understand....

    So like I said, there are often alphabetical sizes (S, M, L, S/M, M/L, and other variations of this), and I am supposed to change them to Small, Medium, Small / Medium, etc. Normally I do this with the Find->Replace tool, but if there was any possible way to somehow put this into a formula it would save tons of time. The sizes generally range from XS-XXL, often times there will be XS/S, S/M, M/L, L/XL, and occasionally S/Long, M/Long, L/Long, XL/Long. If there's no way to do this I wouldn't be surprised, but I figured I may as well ask.

    Again thanks for the help!
    Reply With QuoteRegister To Reply
  6. 07-07-2011, 03:50 PM #6
    NBVC
    NBVC is offline
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: modified MID or REPLACE formula

    If you can create a table that includes all the possible combinations and in adjacent column enter the corresponding equivalents you want returned then you can use VLOOKUP to get that info... try:

    Please Login or Register  to view this content.
    Where R4:S14 would contain the aforementioned table...

    adjust as needed.
    Reply With QuoteRegister To Reply
  7. 07-07-2011, 03:54 PM #7
    drewz5
    drewz5 is offline
    Registered User
    Join Date
    07-06-2011
    Location
    California
    MS-Off Ver
    Excel 2010
    Posts
    4

    Re: modified MID or REPLACE formula

    Quote Originally Posted by NBVC View Post
    If you can create a table that includes all the possible combinations and in adjacent column enter the corresponding equivalents you want returned then you can use VLOOKUP to get that info... try:

    Please Login or Register  to view this content.
    Where R4:S14 would contain the aforementioned table...

    adjust as needed.
    cool, I'll give that a shot. Thanks for the help, makes my work way easier / faster!
    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)

Tags for this Thread

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