Help cleaning up some Strings
Hi ZOCOR,
You need a macro
Sub NoUnderscores()
Dim Rw, Col
Rw = ActiveCell.Row: Col = ActiveCell.Column
Do
Cells(Rw, Col) = Val(Cells(Rw, Col))
Rw = Rw + 1
Loop Until Cells(Rw, Col) = ""
End Sub
Copy the above into a standard module, select the top cell in the column
that containd your data and run the macro.
It goes down your list and stops when it comes to an empty cell.
As it is, the strings will be converted to numbers.
To keep them as strings, replace Val(Cells(rw, Col)) with
Str(Val(Cells(Rw, Col)))
regards,
Don
"ZOCOR" wrote in message
...
Where do you insert this line of code in excel? in the text box?
ZOCOR
"Don Lloyd" wrote in message
...
Hi,
If all the cells are in the format shown then extracting the value will
convert it to a number, minus underscores.
e.g. activecell= Val(activecell)
Should you want to keep it as as text then Activecell=
Str(Val(Activecell))
would do it.
If neither of the above please post back.
regards,
Don
"43N79W" wrote in message
...
Excel 2002
Windows XP Pro
Suppose in column A I have the following kinds of String values
12________
24___
17______
34__
24____
I'd like to write a Macro that will remove all of the underscores
(char(95)
i believe). How would you go about doing that? The number of
underscores
in any given string will vary, but they'll always be at the end of the
string.
TIA
-gk-
--
=================================================
The creative act is not the province of remote oracles or rarefied
geniuses
but a transparent process that is open to everyone.
-Greg Kot in Wilco Learning How To Die-
|