Help cleaning up some Strings
 

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-

Help cleaning up some Strings
 

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-

Help cleaning up some Strings
 

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-

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-

Help cleaning up some Strings
 

How about just recording a macro when you:

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




43N79W wrote:

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-

--

Dave Peterson

Help cleaning up some Strings
 

"Dave Peterson" wrote in message
...
How about just recording a macro when you:

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

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-

Help cleaning up some Strings
 

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

(or the wrong range selected?)

It worked fine for me.

38N90W wrote:

"Dave Peterson" wrote in message
...
How about just recording a macro when you:

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

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-

--

Dave Peterson