Removing key strokes

[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

 

[Harlan Grove]

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.

....

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.

....

How do these dates appear in the formula bar?

 

[Harlan Grove]

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

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.

 

[Random]

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...

 

[Harlan Grove]

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...

...

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.

 

[Harlan Grove]

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.

...

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.

 

[Harlan Grove]

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)

...

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.