+ Reply to Thread
Results 1 to 4 of 4

Commas, Decimals and Standard Number with nothing -- Output separated data

Hybrid View

  1. #1
    Registered User
    Join Date
    04-23-2013
    Location
    United States
    MS-Off Ver
    Excel 2013
    Posts
    16

    Cool Commas, Decimals and Standard Number with nothing -- Output separated data

    Good Morning Everyone. It has been awhile since I have had to use an excel forum for questions. Thank you inadvance for the help of the community.
    I am tasked with the following challenge. Column A is my original data. I need to output Column B and Column C with Formulas based on the following rules.

    Rules:
    If there is a comma split data before the comma in to Column A and the data after the comma into column B
    The data in Column A (on both sides of the comma) can be an interger or a decimal
    The data in Column A may not have a comma

    If there is no comma enter the number value in Column A and Enter "0" Zero in Column B


    1.PNG

    I have the following data in column A (I have decimals sometimes, comma sometimes and no commas and no decimals sometimes)

    50,35
    50,35
    50,35
    40
    52.5
    50.6
    50
    50,40
    50,60
    50,60.5



    I need the output in Column B to be as follows

    50
    50
    50
    40
    52.5
    50.6
    50
    50
    50
    50


    I need the output in Column C to be as follows
    35
    35
    35
    0
    0
    0
    0
    40
    60
    60.5


    I am truly stumped on this one. Any help would be greatly appreciated.

  2. #2
    Valued Forum Contributor Lemice's Avatar
    Join Date
    04-13-2013
    Location
    Somewhere.
    MS-Off Ver
    Excel 2016
    Posts
    696

    Re: Commas, Decimals and Standard Number with nothing -- Output separated data

    Hello,

    Assuming your data is from A1 to A10, you can use this formula in B1
    =IFERROR(LEFT(A1,FIND(",",A1,1)-1)*1,A1)
    and this one in C1
    =IFERROR(RIGHT(A1,LEN(A1)-LEN(B1)-1)*1,0)
    Then drag them down.
    (copy pasta from Ford)
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools

    Regards,
    Lem

  3. #3
    Valued Forum Contributor
    Join Date
    10-29-2012
    Location
    Mojokerto,Indonesia
    MS-Off Ver
    Excel 2007
    Posts
    554

    Re: Commas, Decimals and Standard Number with nothing -- Output separated data

    the first formula at B2
    =IFERROR(LEFT(A2,FIND(",",A2)-1),A2)
    the first formula at C2
    =IFERROR(MID(A2,FIND(",",A2)+1,LEN(A2)),0)
    all copy down

  4. #4
    Registered User
    Join Date
    04-23-2013
    Location
    United States
    MS-Off Ver
    Excel 2013
    Posts
    16

    Re: Commas, Decimals and Standard Number with nothing -- Output separated data

    Thank you.
    Thank you.
    Thank you.

    Magical!

    Again Thank you.

+ Reply to 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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1