Hello, I have tried various things to enable the column to be numbers only so that I can sum it but nothing has worked.Tried clean,trim,text to cols etc.It appears col A is a mixture of text and number formats?If I ask if it is text I get mixed results.In col D I have manually entered numbers and the result of sum is accurate and istext checking is too.This is only a small listing of my A column.Hoping someone has the time to assist please.
Last edited by plato; 02-01-2012 at 02:52 AM. Reason: solved
try
=IF(ISERROR(SUBSTITUTE(SUBSTITUTE(A2,"",""),CHAR(160),"")+0),"",SUBSTITUTE(SUBSTITUTE(A2,"",""),CHAR (160),"")+0)
"Unless otherwise stated all my comments are directed at OP"
Mojito connoisseur and a dabbler in Cisco
where does code go ?
look here
how to insert code
how to enter array formula
why use -- in sumproduct
recommended reading
wiki Mojito
how to say no convincingly
most important thing you need
Martin Wilson: SPV
and RSMBC
Hello Martin,Thank you for prompt reply. This clears it all up,however for some reason when I enter the formula a dialogue box appears asking if I want to correct the formula to what is listed in the box.I cannot see where the formula I pasted is different from the error box.I click on yes and the formula is OK.
As there are a number of these columns on my sheet I wonder if you could advise if there is a quicker way to accomplish the task than by entering the formula in the adjoining column
Try this array formula in B1
Confirm with Ctrl+Shift+Enter not just Enter.=SUM(IF(ISERROR(SUBSTITUTE(SUBSTITUTE(A1:A100,"$",""),CHAR(160),"")*1),0,SUBSTITUTE(SUBSTITUTE(A1:A100,"$",""),CHAR(160),"")*1))
[EDIT]
Excel detects an incorrect space in Martins' formula and is asking to remove it
Might be better with=IF(ISERROR(SUBSTITUTE(SUBSTITUTE(A2,"",""),CHAR(160),"")+0),"",SUBSTITUTE(SUBSTITUTE(A2,"",""),CHAR (160),"")+0)
If you go with a helper column.=IF(ISERROR(SUBSTITUTE(SUBSTITUTE(A2,"$",""),CHAR(160),"")+0),"",SUBSTITUTE(SUBSTITUTE(A2,"$",""),CHAR(160),"")+0)
Last edited by Marcol; 01-25-2012 at 05:42 AM.
If you need any more information, please feel free to ask.
However, if this takes care of your needs, please click EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED. It helps everybody! ....
Also
If you are satisfied by any members response to your problem please consider using the small Star icon botom left of thier post to show your appreciation.
Hello Marcol,
Thanks for that. I have at least three columns on my sheet that have this problem.Have attached sheet ,this is from a larger sheet that has more columns with different text info.Is there a way to have all these columns fixed at once?Or is there a way the whole sheet can be fixed,in case I add more columns later?
What are you asking?
Apply the formula I gave you to each column that you need to sum.
If you want to clean the affected columns
Select the all the columns you need to clean
Use Edit > Replace
Replace:=
Replace With:=*$
Leave this empty
Press Replace All
You can now sum the columns as norrmal e.g. =SUM(A:A)
If you need any more information, please feel free to ask.
However, if this takes care of your needs, please click EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED. It helps everybody! ....
Also
If you are satisfied by any members response to your problem please consider using the small Star icon botom left of thier post to show your appreciation.
tyr this and tell me if this works for you
There is no substitute for clean data.
@ Charlie_Howell
Don't you think that Edit > Replace as I suggested in post #6 would be easier?
Looks better your way
There is no substitute for clean data.
just fyi
=IF(ISERROR(SUBSTITUTE(SUBSTITUTE(A2,"",""),CHAR(160),"")+0),"",SUBSTITUTE(SUBSTITUTE(A2,"",""),CHAR (160),"")+0)
has a space in it! between char and second (160) thats why you couldn't see the difference
=IF(ISERROR(SUBSTITUTE(SUBSTITUTE(A2,"",""),CHAR(160),"")+0),"",SUBSTITUTE(SUBSTITUTE(A2,"",""),CHAR(160),"")+0)
"Unless otherwise stated all my comments are directed at OP"
Mojito connoisseur and a dabbler in Cisco
where does code go ?
look here
how to insert code
how to enter array formula
why use -- in sumproduct
recommended reading
wiki Mojito
how to say no convincingly
most important thing you need
Martin Wilson: SPV
and RSMBC
Hello,Thank you all for your efforts.Problem solved.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks