dslreports logo
site
 
    All Forums Hot Topics Gallery
spc

spacer




how-to block ads


Search Topic:
uniqs
291
share rss forum feed

zach3
Zach
Premium
join:2000-05-04
Saint Louis, MO

Cells Typed As Arguments???

download1141723769.xls.zip 3,172 bytes
I am attempting to analyze my AT&T bill which I have downloaded as an Excel Spreadsheet and when I place a formula below the column of "Min" to Sum the column I get a "0".

Now if I retype each number in each cell I am able to get the correct total.

I have never run into this on a spreadsheet before but it looks to me like AT&T has placed all of the data in their spreadsheets so one cannot easily analyze it in any fashion...

Has anyone run into this before and if so do you have a quick trick to work around the problem???

Thanks,

Zach


thephantom

join:2001-04-24
Alamo, CA
The problem is what appears to be a number in the minutes column has been made into a text field because they added a space character at the end of each number ("15 " instead of "15").
You can easily set up a helper column with a formula to strip off the last blank. For example, in column I, put this formula on row 7:
=VALUE(LEFT(G7,LEN(G7)-1))
and copy that down the column. You will be able to sum that column and get your answer.

zach3
Zach
Premium
join:2000-05-04
Saint Louis, MO
Thank you sir!!! I was looking for something added to the left not the right!! Duh.

They have added a single space to each cell's content.

Is there a quick MACRO one could write that would eliminate all of the spaces in each cell???

Zach


thephantom

join:2001-04-24
Alamo, CA
I'm not real good with macros - I just kind of force something along until it works. I have no idea if there is a more efficient way to do this, or if this will always work, but you can start with it and see what happens.

Sub removespaces()

Dim rngCell As Range
Cells(Rows.Count, "G").End(xlUp).Select
Range(Selection, Selection.End(xlUp)).Select

For Each rngCell In Selection
rngCell.Value = Replace(rngCell.Value, Chr(160), "")
Next

End Sub

The assumptions I made are that the Minute column is always going to be column G. If it isn't, then this will need to be modified. Also, I found that the character in the cell isn't a "normal" space; it was a special character that was an ascii character 160 (I have no idea why). Again, if that changes this won't work. You can run the macro and at least in the sample file you sent it will change all the entries in column G to numbers at which point you can just sum them up as you would normally. One other point is that the lines with the totals at the bottom are merged cells. You may need to work around that; I don't know exactly how you will want to use the sheet, so I wasn't sure I should bother adding that into the macro.
good luck.


RWild
Them Or Us
Premium
join:2003-09-15
Cary, NC
reply to zach3
What about selecting the range interest and using the menu command "find/replace"? >replace " " with { nothing here }.

This is not as specific as thephantom See Profile but is quick and no formulas.

zach3
Zach
Premium
join:2000-05-04
Saint Louis, MO
reply to zach3
Thanks to you both. I opted for RWild's approach by copying and pasting the character in to the "Replace" function and it replaces everything instance in the spreadsheet.

Each and every month has the same character so the fix is easy and once run once using "Replace All" I can use all of the math functions that I want!!!

Looks like to me another way AT&T attempts to mess with customers that want to break down their bills and do "what if" calculations.

Thanks Again,,

Zach