Removing key strokes

  • Thread starter Thread starter Eve
  • Start date Start date
E

Eve

I work with large spreadsheets recieved via e mail and
this is a problem that I am encountering.

I have a column titled city and it contains not only the
city but a state, example: Washington,DC.

I sure would like to know how to remove the comma and the
state name without having to manually remove that from
each row. Oh, by the way the city and state is different
in each row so I can not just make a correction and then
copy and paste.

I also have a column titled date and again the dates in
the column are all different and they all contain extra
000's after the date, example 1/02/1900 000000. Again,
how do I remove the extra keystrokes without having to do
it manually.

As, you can probably tell I am new to excel and have been
learing this program on my own. So, far I have figured
most of it out but this sure stumps me.

Thanks for any help

Eve
 
Eve said:
I have a column titled city and it contains not only the
city but a state, example: Washington,DC.

I sure would like to know how to remove the comma and the
state name without having to manually remove that from
each row. Oh, by the way the city and state is different
in each row so I can not just make a correction and then
copy and paste.
Click to expand...
....

Is the only comma in these cells the one between the city and the state? I
so, you could select the range, then use Edit > Replace, finding ,* (that's
just a comma followed by an asterisk), and replacing it with nothing (that
is, clear the contents of the Replace with field).

I also have a column titled date and again the dates in
the column are all different and they all contain extra
000's after the date, example 1/02/1900 000000. Again,
how do I remove the extra keystrokes without having to do
it manually.
Click to expand...
....

How do these dates appear in the formula bar?
 
ijb said:
the following will remove all text after the comma (and the comma) from a
column of cells (in the example column A)
i = 1
While Cells(i, 1) <> ""
mystr = Cells(i, 1)
If InStr(1, mystr, ",") <> 0 Then
While Right(mystr, 1) <> ","
mystr = Left(mystr, Len(mystr) - 1)
Wend
mystr = Left(mystr, Len(mystr) - 1)
Cells(i, 1) = mystr
End If
i = i + 1
Wend
InStr ....
Not MCSD just experienced
Click to expand...

Giving someone new to Excel macro code (not even a complete macro, and the
dangling InStr?) . . .

As for the code, I don't remember ever seeing a more inefficient way to lop
off the right side of a string.

Sub chopstate()
'select range of cells to batch edit then run this macro
Dim c As Range, n As Long

If Not TypeOf Selection Is Range Then Exit Sub

For Each c In Selection.Cells
If Not (c.HasFormula Or IsNumeric(c.Value)) Then
n = InStr(1, c.Value, ",")
If n > 0 Then c.Value = Left(c.Value, n - 1)
End If
Next c
End Sub

Your non-MCSD experience seems not to have included sufficient face time
with irate users. No doubt that'll come.
 
Uhh... newbie answer here, but if the only object is to save a few
keystrokes and not automate the entire procedure, wouldn't the most
effective way be to use "Text-to-Columns"? At least for the City,
State part that is...
 
Uhh... newbie answer here, but if the only object is to save a few
keystrokes and not automate the entire procedure, wouldn't the most
effective way be to use "Text-to-Columns"? At least for the City,
State part that is...
Click to expand...
...

If you want to save the state part, you're correct. As I read it, the OP asked
for ways to delete the state part. To do that, if the first comma in these cells
were the separator between city and state, then my earlier suggestion to use
Edit > Replace, finding ,* and replacing it with nothing would take the fewest
keystrokes/mouse actions.
 
Apologies for providing a working solution to your question (sorry about the
superfluous "Instr" at the end - bad copy). Harlan is correct, my code will
work significantly slower than his.
Click to expand...
...

Had you included 'Sub someprocname()' at the top and 'End Sub' at the bottom,
then it would have been a working solution. As posted, it was multiple syntax
errors.
 
Or delete it from the data>text to column wizard in step 3,
select the part with the states and check do not import column (skip)
Click to expand...
...

This works, but it becomes a bit of a chore if the data isn't all in a single
column. It probably is, so this isn't a weighty potential drawback. However,
select the range (as you'd need to do to use Text to Columns), [Ctrl]+H, ,* ,
[ab], [Delete], [Enter] still compares favorably to Text to Columns in XL97,
which doesn't provide accelerator keys for Next and Back buttons.
 
Back
Top