+ Reply to Thread
Results 1 to 11 of 11

Reverse Complex Data In a Cell

  1. #1
    Registered User
    Join Date
    02-05-2020
    Location
    Phoenix, AZ
    MS-Off Ver
    office 365
    Posts
    17

    Question Reverse Complex Data In a Cell

    This should be an easy one for you experts. Lets say I have data in a column like this
    Example:
    Axle Truss|Axle|Drivetrain & Differentials

    Lets say I need to take that whole column and reverse it to look like this:
    Drivetrain & Differentials|Axle|Axle Truss

    I started with something like this but it doesn't work right and needs some tweaking but I don't really get how to tweak it.
    =RIGHT(J2,LEN(J2)-FIND("",J2))&" "&LEFT(J2,FIND("",J2)-1)

    Thank you!

  2. #2
    Forum Expert
    Join Date
    07-06-2004
    Location
    Northern California
    MS-Off Ver
    2K, 2003, 2010, O365
    Posts
    1,490

    Re: Reverse Complex Data In a Cell

    If you mean you have Axle Truss|Axle|Drivetrain & Differentials in a single cell and you want to reverse the order of the substrings between the vertical bars ( | ), you could use

    =REPLACE(J2,1,FIND("|",J2,FIND("|",J2)+1),"")&MID(J2,FIND("|",J2),FIND("|",J2,FIND("|",J2)+1)-FIND("|",J2)+1)&LEFT(J2,FIND("|",J2)-1)

    OTOH, given your Excel version, if these were in separate cells, J2:L2, it'd be A LOT SIMPLER,

    J3: =INDEX(J2:L2,SEQUENCE(1,3,3,-1)

    This would spill into J3:L3.

    The first alternative (single cell) is an example of text manipulation which Excel does rather poorly compared to other spreadsheets. LibreOffice Calc (latest version) could handle the former with

    =TEXTJOIN("|",0,REGEX(J2,"[^|]+",,{3,2,1}))

    If you need to do this sort of thing A LOT, do you need to use Excel to do it? If so, get used to long formulas or VBA.

  3. #3
    Registered User
    Join Date
    02-05-2020
    Location
    Phoenix, AZ
    MS-Off Ver
    office 365
    Posts
    17

    Re: Reverse Complex Data In a Cell

    Thank you! I am going to have to use excel a lot. I have a Jeep parts online store and uploading data sheets is a real chore. Let me ask you this, if I had one cell called "Fitment" and in that cell it looks like this:

    fitment
    2007,2017,Jeep,Wrangler

    Now, this is an issue because the way it imports this into woocommerce it creates 2007 as a parent on its own and no children but then once it gets passed 2007 it creates 2017 with Jeep then Wrangler as children and that is correct.
    I almost need some crazy cool formula that sees the above example and does this:
    2007,Jeep,Wrangler|2008,Jeep,Wrangler|2009,Jeep,Wrangler so on and so fourth.

    The sheets can have 1200 products and its just crazy. Here is what it really looks like in the fitment cell.

    2007,2017,Jeep,Wrangler,|2007,2016,Jeep,Wrangler,Unlimited Rubicon|2007,2016,Jeep,Wrangler,Unlimited Sahara|2007,2010,Jeep,Wrangler,Unlimited X|2010,2016,Jeep,Wrangler,Unlimited Sport|2011,2011,Jeep,Wrangler,Unlimited 70th Anniversary|2016,2016,Jeep,Wrangler,Unlimited 75th Anniversary|2016,2016,Jeep,Wrangler,Unlimited Sport S|2018,2018,Jeep,Wrangler JK,

    Any ideas?

  4. #4
    Forum Guru
    Join Date
    04-23-2012
    Location
    New Jersey, USA
    MS-Off Ver
    Excel 365
    Posts
    2,401

    Re: Reverse Complex Data In a Cell

    Quote Originally Posted by cjs1031 View Post
    The sheets can have 1200 products and its just crazy. Here is what it really looks like in the fitment cell.

    2007,2017,Jeep,Wrangler,|2007,2016,Jeep,Wrangler,Unlimited Rubicon|2007,2016,Jeep,Wrangler,Unlimited Sahara|2007,2010,Jeep,Wrangler,Unlimited X|2010,2016,Jeep,Wrangler,Unlimited Sport|2011,2011,Jeep,Wrangler,Unlimited 70th Anniversary|2016,2016,Jeep,Wrangler,Unlimited 75th Anniversary|2016,2016,Jeep,Wrangler,Unlimited Sport S|2018,2018,Jeep,Wrangler JK,
    I wasn't clear if you wanted the values split out into individual cells (what the code below does) or within a single cell (can't test with TEXTJOIN function as I do not have a version of Excel with it, but I think the formula could be modified to use it). So, assuming your data is in cell A2 and you wanted it split out to individual cells, does this formula placed in cell B2 and copied across do it for you...
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Last edited by Rick Rothstein; 02-07-2020 at 04:51 PM.

  5. #5
    Forum Expert
    Join Date
    07-06-2004
    Location
    Northern California
    MS-Off Ver
    2K, 2003, 2010, O365
    Posts
    1,490

    Re: Reverse Complex Data In a Cell

    Quote Originally Posted by cjs1031 View Post
    . . .
    fitment
    2007,2017,Jeep,Wrangler

    I almost need some crazy cool formula that sees the above example and does this:
    2007,Jeep,Wrangler|2008,Jeep,Wrangler|2009,Jeep,Wrangler so on and so fourth.
    . . .
    The sheets can have 1200 products and its just crazy. Here is what it really looks like in the fitment cell.

    2007,2017,Jeep,Wrangler,|2007,2016,Jeep,Wrangler,Unlimited Rubicon|2007,2016,Jeep,Wrangler,Unlimited Sahara|2007,2010,Jeep,Wrangler,Unlimited X|2010,2016,Jeep,Wrangler,Unlimited Sport|2011,2011,Jeep,Wrangler,Unlimited 70th Anniversary|2016,2016,Jeep,Wrangler,Unlimited 75th Anniversary|2016,2016,Jeep,Wrangler,Unlimited Sport S|2018,2018,Jeep,Wrangler JK,

    Any ideas?
    First idea: Excel sucks at text processing compared to most scripting languages. Do you really need to do this in Excel? If you're working with a web site, this may be an outstanding opportunity to learn JavaScript.

    If you need to convert 2007,2017,Jeep,Wrangler into 2007,Jeep,Wrangler|2008,Jeep,Wrangler|...|2017,Jeep,Wrangler, then if the fitment cell were A1,

    =IF(COUNT(-MID(A1,1,4),-MID(A1,6,4))=2,TEXTJOIN("|",0,SEQUENCE(MID(A1,6,4)-MID(A1,1,4)+1,1,--MID(A1,1,4))&REPLACE(A1,1,9,"")),A1)

    As for your real fitment cell, is the comma just before the vertical bar mean there'd be a blank field following Wrangler? Looks like there'd be model variations following base model name, and that could be empty or omitted entirely. Is that correct?

    Anyway, your real fitment cell looks like multiple records separated by vertical bars with 4 or 5 fields in each record: 1) starting model year, 2) ending model year, 3) company, 4) base model, 5) variation [optional]. Would you really need an extremely long result in a single cell? Or could you work with every record BETWEEN vertical bars in separate cells? TBH, if you really need to process the whole cell as a single cell and return a huge result, you should consider using VBA. It may be possible to do this with formulas, but the formula would be nearly as long as the results.

    If you could split this into multiple cells, use Text to Columns with | as the delimiter to split into multiple cells in sequential columns in the row. Then use the formula above to process each cell, then use another formula to recombine them. If your original cell were A11, use Text to Columns to split it into A11:I11. Then

    AA11: =IF(COUNT(-MID(A11,1,4),-MID(A11,6,4))=2,TEXTJOIN("|",0,SEQUENCE(MID(A11,6,4)-MID(A11,1,4)+1,1,--MID(A11,1,4))&REPLACE(A11,1,9,"")),A11)

    Fill AA11 right into AB11:AI11. Then recombine them.

    BA1: =TEXTJOIN("|",0,AA11:AI11)

    Which produces the monster result (line brakes added for readability, but it's one long string)

    2007,Jeep,Wrangler,|2008,Jeep,Wrangler,|2009,Jeep,Wrangler,|2010,Jeep,Wrangler,|2011,Jeep,Wrangler,|2012,Jeep,Wrangler,|2013,Jeep,Wrangler,|
    2014,Jeep,Wrangler,|2015,Jeep,Wrangler,|2016,Jeep,Wrangler,|2017,Jeep,Wrangler,|2007,Jeep,Wrangler,Unlimited Rubicon|
    2008,Jeep,Wrangler,Unlimited Rubicon|2009,Jeep,Wrangler,Unlimited Rubicon|2010,Jeep,Wrangler,Unlimited Rubicon|2011,Jeep,Wrangler,Unlimited Rubicon|
    2012,Jeep,Wrangler,Unlimited Rubicon|2013,Jeep,Wrangler,Unlimited Rubicon|2014,Jeep,Wrangler,Unlimited Rubicon|2015,Jeep,Wrangler,Unlimited Rubicon|
    2016,Jeep,Wrangler,Unlimited Rubicon|2007,Jeep,Wrangler,Unlimited Sahara|2008,Jeep,Wrangler,Unlimited Sahara|2009,Jeep,Wrangler,Unlimited Sahara|
    2010,Jeep,Wrangler,Unlimited Sahara|2011,Jeep,Wrangler,Unlimited Sahara|2012,Jeep,Wrangler,Unlimited Sahara|2013,Jeep,Wrangler,Unlimited Sahara|
    2014,Jeep,Wrangler,Unlimited Sahara|2015,Jeep,Wrangler,Unlimited Sahara|2016,Jeep,Wrangler,Unlimited Sahara|2007,Jeep,Wrangler,Unlimited X|
    2008,Jeep,Wrangler,Unlimited X|2009,Jeep,Wrangler,Unlimited X|2010,Jeep,Wrangler,Unlimited X|2010,Jeep,Wrangler,Unlimited Sport|
    2011,Jeep,Wrangler,Unlimited Sport|2012,Jeep,Wrangler,Unlimited Sport|2013,Jeep,Wrangler,Unlimited Sport|2014,Jeep,Wrangler,Unlimited Sport|
    2015,Jeep,Wrangler,Unlimited Sport|2016,Jeep,Wrangler,Unlimited Sport|2011,Jeep,Wrangler,Unlimited 70th Anniversary|
    2016,Jeep,Wrangler,Unlimited 75th Anniversary|2016,Jeep,Wrangler,Unlimited Sport S|2018,Jeep,Wrangler JK,

  6. #6
    Registered User
    Join Date
    02-05-2020
    Location
    Phoenix, AZ
    MS-Off Ver
    office 365
    Posts
    17

    Re: Reverse Complex Data In a Cell

    I agree that Excel is not good for this but your solution looks like it may work. Can I send you the complete data sheet so you can get eyes on and give me some advice on how to handle that column? The problem is this...I get my data sheets from someone that already forms them and they come in excel. They are putting an API together later and right now I have to do this manually.

  7. #7
    Forum Expert
    Join Date
    07-06-2004
    Location
    Northern California
    MS-Off Ver
    2K, 2003, 2010, O365
    Posts
    1,490

    Re: Reverse Complex Data In a Cell

    Quote Originally Posted by cjs1031 View Post
    . . . Can I send you the complete data sheet so you can get eyes on and give me some advice on how to handle that column? . . .
    If this is sensitive data, or proprietary from a 3rd party source, do you really want to trust me and/or are there no IP restrictions on sending it to anyone not contracted with your 3rd party source? If it's not sensitive/proprietary, why not post it publicly here?

  8. #8
    Registered User
    Join Date
    02-05-2020
    Location
    Phoenix, AZ
    MS-Off Ver
    office 365
    Posts
    17

    Re: Reverse Complex Data In a Cell

    Here is an example

  9. #9
    Registered User
    Join Date
    02-05-2020
    Location
    Phoenix, AZ
    MS-Off Ver
    office 365
    Posts
    17

    Re: Reverse Complex Data In a Cell

    Ok should be there on this one
    Attached Files Attached Files

  10. #10
    Registered User
    Join Date
    02-05-2020
    Location
    Phoenix, AZ
    MS-Off Ver
    office 365
    Posts
    17

    Re: Reverse Complex Data In a Cell

    Its not sensitive

  11. #11
    Forum Expert
    Join Date
    07-06-2004
    Location
    Northern California
    MS-Off Ver
    2K, 2003, 2010, O365
    Posts
    1,490

    Re: Reverse Complex Data In a Cell

    See this workbook on OneDrive.

    I added a 2nd worksheet for processing the fitment column (O) from Sheet1. Sheet2!A2:U13 parse out the original values into separate cells with just

    begin_yyyy,end_yyyy,make,model[,variation]

    A2:U13 show -- when data has been exhausted on that row. Intermediate formulas in AA2:AT2 create multiple |-separated sections for yyyy,make,model[,variation] between begin_yyyy and end_yyyy. Final formulas in BA2:BA12 combine intermediate results into final |-separated results. You could paste Sheet2!BA2:BA12 and paste special as values back into Sheet1!O2:O12.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. I need help figuring a complex (for me) reverse discount formula
    By JBarryD in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 09-14-2015, 07:59 AM
  2. Excel 2007 : Is it possible to reverse data in a cell
    By jjjason10 in forum Excel General
    Replies: 5
    Last Post: 09-09-2015, 08:28 AM
  3. Complex Reverse tax de calculation
    By witchcraftz in forum Excel - New Users/Basics
    Replies: 2
    Last Post: 04-09-2014, 04:19 PM
  4. [SOLVED] reverse order of data in one cell
    By lostinexcel77 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 08-23-2012, 11:41 AM
  5. Reverse data in cell
    By lioninstreet in forum Excel General
    Replies: 4
    Last Post: 10-18-2011, 10:45 PM
  6. Reverse Data In A Cell / Reverse Cell Contents
    By nostawydoc in forum Excel General
    Replies: 5
    Last Post: 12-18-2009, 05:20 PM
  7. Reverse Data Within Cell By Date
    By nobi in forum Excel Programming / VBA / Macros
    Replies: 16
    Last Post: 01-21-2009, 06:28 PM

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1