Data Validation: Number Conversion

  • Hi all
    I have attached a file.
    Pls take a look


    Column A is the raw data i pulled out from the database.


    I need to convert them into format in Column B.


    For instance, the raw data is 1.236,000
    it represents 1236
    I need excel to trim of all the back portion of ,000 and regognise 1.236 as 1236.


    Bear in mind that i would need to values for calculation. I tried dividing by 1000, dun think it helps.


    My raw data goes up to as high as 4500 records.


    Thankz


    Cheers

  • Hi James,


    Copy this formula into B1 to format Cell A1. You can copy this down for the other cells in column A.


    =(IF(ISNUMBER(A1),A1,IF(ISERROR(REPLACE(TRIM(A1),SEARCH(".",TRIM(A1),1),1,"")),VALUE(REPLACE(TRIM(A1),SEARCH(",",TRIM(A1),1),1,"")),VALUE(REPLACE(TRIM(A1),SEARCH(".",TRIM(A1),1),1,"")))))/1000


    Hope this helps,
    Thanks,
    Rennie

  • when i pasted the formula on cell C1 and copied it down.


    I realised that the data with 0, ended up with error, #NAME.


    How to resolve this?
    take a look at the attached file

  • Hi Rennie


    Can you explain to me the parameters of the formulae?


    I am very interested. Where do you get that? Its amazing. Btw, i got the result. Thanks alot.


    =(IF(ISNUMBER(A1),A1,IF(ISERROR(REPLACE(TRIM(A1),SEARCH(".",TRIM(A1),1),1,"")),VALUE(REPLACE(TRIM(A1),SEARCH(",",TRIM(A1),1),1,"")),VALUE(REPLACE(TRIM(A1),SEARCH(".",TRIM(A1),1),1,"")))))/1000

  • =IF(ISERROR(A1/1000),0,A1/1000)


    Try this formula, it will divide by 1000 to remove the,000 and the ISERROR replaces the #VALUE message caused by dividing zero with 0.

  • Hi,


    Sorry for not posting this any urlier. The thought process behind the solution was to make the text string in the cell a numerical value.


    I did this by eliminating the "," and the "." usign the replace function.


    The excel help has comprehensive descriptions of what each fuction does with examples. Please have a look at these.


    Hope this helps.


    Rennie

Participate now!

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