+ Reply to Thread
Results 1 to 4 of 4

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

  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
    Please Login or Register  to view this content.
    and this one in C1
    Please Login or Register  to view this content.
    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