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,
It works if you copy the formula into B1.
Thanks,
Rennie
Hi rennie
I tried pasting the formula into B... the same result, with error, #Name?
rdgs
Hi James,
Sorry for the trouble. Look at the attached file.
Thanks,
Rennie
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 James,
I am a bit busy now. I will post an explanation later. The #Name error was due to a gap in the Trim function. I.e - Make sure the "Trim" does not appear as "T rim".
Thanks,
Rennie
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
Don’t have an account yet? Register yourself now and be a part of our community!
