Help cleaning up some Strings

4

43N79W

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

Don Lloyd

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
 
D

Don Lloyd

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
 
D

Dave Peterson

How about just recording a macro when you:

Select column A
edit|replace
what: _ (underscore)
with: (leave blank)
replace all
 
3

38N90W

Dave Peterson said:
How about just recording a macro when you:

Select column A
edit|replace
what: _ (underscore)
with: (leave blank)
replace all
Click to expand...

I tried that (not even bothering with recording a macro) and it simply
didn't work. The Replace dialog beeped at me and said that it found no
values satisfying the conditions.

The solution is the VBA Replace function. Worked like a charm once I found
it.

Oh, and it wasn't as simple as just doing a LEFT(cell reference,2) since the
number of numerical digits in each string varied from 1 to four.

Thanks for all suggestions though.

-gk-
 
D

Dave Peterson

Any chance you had the Edit|replace option of "match entire cell contents"
checked?

(or the wrong range selected?)

It worked fine for me.
 
Top