Leading characters

  • Thread starter Thread starter Chewy509
  • Start date Start date
C

Chewy509

Hi Everyone,

I would just like to get an explanation for the following, and if
possible a solution, using Excel 2000 with all SP's and hotfixes
applied.

We have our PABX call data dumped into a tab-delimitated text file,
which we import into excel, and using either Crystal Reports or Excel
create charts and various other reports, which works well for us...

However there is one small problem, the call time is imported from the
text file, as "' 00:00:25", which displays in the function bar in the
same format. However on cell displays as " 00:00:25". So why does the
leading "'" not show, and if I attempt a find/replace to remove the
leading "' " from the cell, it fails, because it can't find the string
in the cell. If you don't believe try this:
Open a new sheet in Excel 2000, put "' 00:00:18" into the first cell.
Now try to use find/replace to remove the leading "' " from that cell.
It doesn't work?

So is there a way around this? either by Macro or VBA? I thought bout
modifying the original ile, but "' " appears in other places which I
need to keep. I only need it removed from the call time column.

Also a more general question: when I set the contents of a cell to
"00:00:18" and set it format to time, it gets changed to 12:00:18 AM,
which in a sense is correct, but isn't the format I'm after. I need
the time as a quantive value rather than a clock time value, for
charting. ie phone extension 101 spent 12hrs 43 on STD calls this
month, which would be a sum of all the call times for extension 101.
How do I do this?

TIA
 
the leading ' is treated as a formatting character. Thus it isn't
considered part of the cell.

Select your cells and run this macro:

Sub replaceit()
For Each cell In Selection
cell.Value = Trim(cell.Text)
Next
End Sub

This will convert the value to time values, however - hope that is what you
want.

It only displays in the formula bar as 12:00:18 AM, if it displays in the
cell that way, just format the cell to hh:mm:ss


It won't make any difference if you are performing math on the cells. Time
is stored as the fraction of a day. So an elapsed time is day zero plus the
fraction. 6 am = 0.25 for example. Format the cell as general and you
will see what I mean.
 
the leading ' is treated as a formatting character. Thus it isn't
considered part of the cell.

Select your cells and run this macro:

Sub replaceit()
For Each cell In Selection
cell.Value = Trim(cell.Text)
Next
End Sub

This will convert the value to time values, however - hope that is what you
want.

It only displays in the formula bar as 12:00:18 AM, if it displays in the
cell that way, just format the cell to hh:mm:ss


It won't make any difference if you are performing math on the cells. Time
is stored as the fraction of a day. So an elapsed time is day zero plus the
fraction. 6 am = 0.25 for example. Format the cell as general and you
will see what I mean.
Click to expand...
Thanks Tom.
 
I think your problem is that that "'" denotes that a
literal (or string) value follows. If you enter into a
cell "'=10/2" you will get the 'answer' "=10/2", not "5".
Make sense??

Thus, you can't use a standard find & replace easily.
You could use a really simple and nasty sub like...
Sub test()
ActiveCell.Formula = Right(ActiveCell.Formula, Len
(ActiveCell.Formula))
End Sub
which removes the "'". Turn this into a loop for your
range (or modify to something more suited to your app).

Marco
 
Back
Top