+ Reply to Thread
Results 1 to 6 of 6

assistance to adjust splitting formula in certain column headers which blank cells

  1. #1
    Forum Contributor
    Join Date
    02-11-2014
    Location
    mauritius
    MS-Off Ver
    MS365
    Posts
    1,079

    assistance to adjust splitting formula in certain column headers which blank cells

    I have a worksheet which split pack size drinks in separate columns packs, per packs, litrage and product litrage headers .


    Using formulas works well for splitting but getting issues when pack size description reads with litres , I am getting a blank cells or false in certain cells.

    Thanks if any can assist.
    Attached Files Attached Files

  2. #2
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,903

    Re: assistance to adjust splitting formula in certain column headers which blank cell

    Hi JEAN,

    1. Enter formula in I2 and copy across to L2
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    2. Enter in I6 and copy across and down
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    v I J K L
    1 Packs Per packs Litrage Product Litrage
    2 6 5 2 2
    3
    4
    5
    6 6 5 1 1
    7 6 5 1 1
    8 6 5 1.3 1.3
    9 6 5 1.3 1.3
    10 6 3 1.3 1.3
    11 36 6 2.5 2.5
    12 36 6 3.5 3.5
    Attached Files Attached Files
    If you like my answer please click on * Add Reputation
    Don't forget to mark threads as "Solved" if your problem has been resolved

    "Nothing is so firmly believed as what we least know."
    --Michel de Montaigne

  3. #3
    Forum Contributor
    Join Date
    02-11-2014
    Location
    mauritius
    MS-Off Ver
    MS365
    Posts
    1,079

    Re: assistance to adjust splitting formula in certain column headers which blank cell

    Hi Alkey

    Tried works fine but my issue , it does not gave expected results with the other pack size description. when applied to older pack size it differs.

    See attached highlight in yellow column K and L in yellow it differs from column F and G .

    Is there a way to merge it from the previous formula column D to G
    Attached Files Attached Files

  4. #4
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,900

    Re: assistance to adjust splitting formula in certain column headers which blank cell

    In E2, building on Alkey's excellent suggestions, copied across and down:
    =IFERROR(--MID(SUBSTITUTE("*"&SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(UPPER($A2),"LITRES",""),"LITRE",""),",",".")," ",""),"*",REPT(" ",50)),50*COLUMNS($A:A),50),D2)
    Attached Files Attached Files
    Glenn




    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.

    Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh

  5. #5
    Forum Contributor
    Join Date
    02-11-2014
    Location
    mauritius
    MS-Off Ver
    MS365
    Posts
    1,079

    Re: assistance to adjust splitting formula in certain column headers which blank cell

    Hi Glenn

    AT row 2 column G and H should show 75 AND 0.75 , highlight in green where the results are not good ,previous formula worked but with AlKey formula not giving the right answer for those with 75 and 0.75 as expected results
    Attached Files Attached Files

  6. #6
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,900

    Re: assistance to adjust splitting formula in certain column headers which blank cell

    Ah yes...

    try:

    =IFERROR(1/(1/IFERROR(--MID(SUBSTITUTE("*"&SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(LOWER($A2),"litres",""),"litre",""),"cl",""),",","."),"L",""),"*",REPT(" ",50)),50*COLUMNS($A:A),50),IF(ISNUMBER(SEARCH("cl",$A2)),D2/100,D2))),"")
    Attached Files Attached Files

+ 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. [SOLVED] Adjust formula to add IF, two 0's cells, leave blank
    By Beh162 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 08-26-2017, 07:22 PM
  2. [SOLVED] assistance to adjust vba code values to appear in cells not formulas
    By JEAN1972 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 06-13-2017, 03:48 AM
  3. Replies: 3
    Last Post: 04-19-2017, 09:30 AM
  4. [SOLVED] dynamic vba code to delete duplicate headers, blank headers and blank columns
    By JEAN1972 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 03-14-2017, 02:27 PM
  5. [SOLVED] Refer to cells in formula using column headers
    By surePac in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 09-25-2014, 01:25 PM
  6. [SOLVED] Remove blank cells form range due to Column headers.
    By Nitefox in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 03-11-2014, 09:49 PM
  7. [SOLVED] How to adjust this formula to keep "Not yet entered" cells blank
    By Shrad013 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 06-20-2013, 12:02 PM

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