Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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- |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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- |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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- |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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- |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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- |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
find and replace numeric strings in larger text strings | Excel Worksheet Functions | |||
Cleaning Up Data | Excel Discussion (Misc queries) | |||
How to find number of pairs of strings from list of strings? | Excel Worksheet Functions | |||
Spreadsheet cleaning | Excel Discussion (Misc queries) | |||
Cleaning up data | Excel Programming |