Any formula to convert numbers stored as text

  • I converted a text file to an excel file and found several numbers stored as text with . (period) as thousand separators and , (comma) as decimal separator. And worse, some negative signs is appearing on the right side of the number. (It actually looks like this, 1.566,56-. Correct format should be 1,566.56 as numbers and with enclosed in parenthesis if negative)


    Is there a formula how to convert these "text-numbers" into "numbers" with the correct format ( comma as thousand separator and period as decimal separator)? I tried formatting it from Format Cells but nothing happens.


    Please help

  • If the data are in column A starting in A2 then paste the following formula in B2 and drag down. Format the result to show the commas.
    =IF(RIGHT(A2,1)="-",-1*SUBSTITUTE(SUBSTITUTE(LEFT(A2,LEN(A2)-1),".",""),",","."),1*SUBSTITUTE(SUBSTITUTE(A2,".",""),",","."))

  • Thanks Derk! You're great...!!!!!


    (Sorry for the late reply....got so busy for so many reports....mid-year audit, huh!)


    Hope you can help me on this also...


    I have these data in b10:b20....


    A B C D E
    1
    2
    ...
    10 5 3%
    11 7 2%
    12 11 5%
    13 15 7%
    14 4 1%
    etc


    ...and wanted to multiply column A by column B in cells A2 to J2 (product of a10 by b10 will be in A2, a11 by b11 in B2, etc).


    Is there a single formula that changes the cell reference by rows (not by columns)?


    Thanks again...


    how can i do this in one formula, without changing the cell reference? I'm aware that when we copy formula to the succeeding columns, cell reference would change in columns also and not in rows. hope i made it clear

  • One way is to use an array function. Select cells A2:K2 (need to go to K to get all 11 products) and with A2 the active cell put
    =TRANSPOSE(A10:A20*B10:B20)
    in the formula box and then hold down the Control and Shift keys when you push the Enter key. Excel will put {curly brackets} around the formula to indicate you did it right.

  • Thanks Derk....


    Sorry for the (very, very) late reply...


    Dont know b4 how to locate my thread...



    Any other formula not using array (formula) to accomplish this...???

  • Works great :thumbcoo:


    Thanks Will...
    Also to Derk...


    You guys really deserve ur "titles" (OzMVP, for whatever that means) here in Oz.


    Please take care of your health....
    Lots of us needs you guys... :cool:


    God Bless!!!!

  • Re: Any formula to convert numbers stored as text


    In excel A1 column is 12,450 and I want it to convert in B1 column as twelve thousand four hundred fifty only

  • Re: Any formula to convert numbers stored as text


    excel formula
    A1 column is 12,450.75 and I want it to convert as twelve thousand four hundred fifty and Paisa seventy five only


    A1 column is 12,450 and I want it to convert as twelve thousand four hundred fifty only
    Please help and let me have a formula


    [email protected]

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!