+ Reply to Thread
Results 1 to 7 of 7

Is there a formula to split a column into 2 and multiply numeric part?

  1. #1
    Registered User
    Join Date
    02-14-2012
    Location
    St. Paul, MN
    MS-Off Ver
    Excel 2007
    Posts
    14

    Is there a formula to split a column into 2 and multiply numeric part?

    • If the SKYCONDITION column reports a value of OVC or BKN, then report the last three digits in the CEILING column and multiply by 100.
    o For example, OVC110 should report 11000 in the CEILING column
    • If there are multiple values, use the lowest OVC or BKN numeric value
    o For example, SCT015 SCT020 OVC030 should report 3000 in the ceiling column
    • All other observations (i.e. CLR, FEWXXX, SCTXXX) should report “N/A” in the CEILING column.


    Example of my Excel sheet:


    SkyCondition Ceiling
    SCT110
    OVC110
    OVC100
    CLR
    OVC110
    BKN017 BKN027 OVC041
    OVC012
    CLR
    CLR

  2. #2
    Valued Forum Contributor
    Join Date
    04-27-2015
    Location
    Abu Dhabi, U.A.E
    MS-Off Ver
    Office 365 | 2016
    Posts
    696

    Re: Is there a formula to split a column into 2 and multiply numeric part?

    Please refer the attached sheet.

    Regards
    Shareez
    Attached Files Attached Files

  3. #3
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,396

    Re: Is there a formula to split a column into 2 and multiply numeric part?

    I used helper columns to reconcile the multiple values.

    Row\Col
    A
    B
    C
    D
    E
    F
    1
    ****SCT110
    #N/A
    B1:D9 =IFERROR(--TRIM(MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(($A1),"OVC",""),"BKN","")," ",REPT(" ",256)),(COLUMNS($A:A)-1)*256+1,256)),"")
    2
    ****OVC110
    110
    11000
    E1 =IF(MIN(B1:D1),MIN(B1:D1)*100,#N/A)
    3
    ****OVC100
    100
    10000
    4
    ****CLR
    #N/A
    5
    ****OVC110
    110
    11000
    6
    ****BKN017 BKN027 OVC041
    17
    27
    41
    1700
    7
    ****OVC012
    12
    1200
    8
    ****CLR
    #N/A
    9
    CLR
    #N/A


    Edit I do not know why those leading *****s show up in the grid below. I copied and pasted the data in Post #1. They are not in the workbook.
    Last edited by FlameRetired; 06-10-2015 at 12:41 AM.

  4. #4
    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: Is there a formula to split a column into 2 and multiply numeric part?

    Here is another way

    1. Formula to separate numbers and multiplication

    =IFERROR(SUBSTITUTE(A2,LEFT(A2,3),0)*1000,"")

    2. Formula to extract unique list of 3-letter codes.

    =IFERROR(INDEX(LEFT($A$2:$A$12,3),MATCH(0,INDEX(COUNTIF(C$1:C1,LEFT($A$2:$A$12,3)),,),)),"")

    3. ***Array formula to get minimum value for each code

    =MIN(IF(LEFT($A$2:$A$12,3)=C2,$B$2:$B$12))

    ***Array formula must be entered by using key combination of CTRL+SHIFT+ENTER and not just ENTER

    Row\Col
    A
    B
    C
    D
    1
    Sky Condition Ceiling Sky Condition Ceiling
    2
    SCT110
    110000
    SCT
    110000
    3
    OVC110
    110000
    OVC
    12000
    4
    OVC100
    100000
    CLR
    0
    5
    CLR
    0
    BKN
    17000
    6
    OVC110
    110000
    7
    BKN017
    17000
    8
    BKN027
    27000
    9
    OVC041
    41000
    10
    OVC012
    12000
    11
    CLR
    0
    12
    CLR
    0
    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

  5. #5
    Registered User
    Join Date
    02-14-2012
    Location
    St. Paul, MN
    MS-Off Ver
    Excel 2007
    Posts
    14

    Stuck Again! Is there a formula to split a column into 2 and multiply numeric part?

    I kind of used all of your advice and added guidance text. There is one situation where if we have a FEW/SCT value reported before an OVC/BKN value, the OVC/BKN value that needs to be reported isn’t displayed.

    Can anyone offer solutions for this?
    Attached Files Attached Files

  6. #6
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,396

    Re: Stuck Again! Is there a formula to split a column into 2 and multiply numeric part?

    This works with the latest upload.

    Row\Col
    A
    B
    C
    D
    E
    F
    G
    1
    SkyCondition
    Helper columns
    Formula Result Required Result
    2
    SCT110 In B2 =IFERROR(--TRIM(MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(($A2),"OVC",""),"BKN","")," ",REPT(" ",256)),(COLUMNS($A:A)-1)*256+1,256)),"")
    3
    OVC110
    110
    11000
    11000
    filled down and across to D11.
    4
    OVC100
    100
    10000
    10000
    In E2 =IF(MIN(B2:D2),MIN(B2:D2)*100,"")
    5
    CLR filled down to E11.
    6
    OVC110
    110
    11000
    11000
    7
    BKN017 BKN027 OVC041
    17
    27
    41
    1700
    1700
    8
    OVC012
    12
    1200
    1200
    9
    SCT080 BKN110
    110
    11000
    11000
    10
    SCT075 OVC110
    110
    11000
    11000
    11
    FEW050

  7. #7
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,396

    Re: Stuck Again! Is there a formula to split a column into 2 and multiply numeric part?

    This does it in one formula…no helper columns. It is an array formula entered into cell B2 in the attached and filled down.
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Array enter means the formula must be committed by simultaneously pressing and holding down Ctrl and Shift while hitting Enter.
    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] Formula to multiply column with a dynamic column
    By keis386 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 05-20-2015, 09:05 AM
  2. Find alphanumeric values in one column and split alpha from numeric
    By bowdendavid in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 02-08-2013, 03:23 PM
  3. Replies: 5
    Last Post: 06-10-2010, 09:45 AM
  4. Multiply text values with numeric substitutions
    By jheddings in forum Excel General
    Replies: 2
    Last Post: 07-11-2009, 07:08 PM
  5. split a list into multiply sheets
    By yrndtn in forum Excel General
    Replies: 1
    Last Post: 04-22-2009, 09:09 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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1