Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
editing cells...
Hey all, I've been reading thru tutorials and tips and forums for a fe hours now and haven't found anything to help with my problem. I Am ne to excel vba, yet believe my problem should be easy to solve with macro. Here's the deal: I'm importing simple data-sets into Excel and fin that each cell value includes a ' at the beginning of the value ... eg ['150.234 in]. I would like to create a macro that edits the cell, so deleting th first character ['] and last three characters [ in]. If someone could point me in the right direction ... with an exampl macro, or where to find one for cell editing ... I'd appreciate it. thanks. lober ----------------------------------------------- ~~ Message posted from http://www.ExcelTip.com ~~View and post usenet messages directly from http://www.ExcelForum.com |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
editing cells...
Try something like this for the selection
Sub test() For Each cell In Selection On Error Resume Next cell.Value = Left(cell.Value, Len(cell.Value) - 3) cell.Value = Right(cell.Value, Len(cell.Value) - 1) On Error GoTo 0 Next End Sub -- Regards Ron de Bruin (Win XP Pro SP-1 XL2002 SP-2) www.rondebruin.nl "jloberg" wrote in message ... Hey all, I've been reading thru tutorials and tips and forums for a few hours now and haven't found anything to help with my problem. I Am new to excel vba, yet believe my problem should be easy to solve with a macro. Here's the deal: I'm importing simple data-sets into Excel and find that each cell value includes a ' at the beginning of the value ... eg= ['150.234 in]. I would like to create a macro that edits the cell, so deleting the first character ['] and last three characters [ in]. If someone could point me in the right direction ... with an example macro, or where to find one for cell editing ... I'd appreciate it. thanks. loberg ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~View and post usenet messages directly from http://www.ExcelForum.com/ |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
editing cells...
loberg
This macro will strip all but numbers and the decimal point. Well, not quite.......it will also leave /(asc 47) if present. You don't have any of these /'s in your example so no problem<g Took about 15 seconds to convert 222,000 cells on 2.6Ghz machine Public Sub StripAllAZs() Dim myRange As Range Dim cell As Range Dim myStr As String Dim i As Integer With Application .ScreenUpdating = False .Calculation = xlManual End With On Error Resume Next Set myRange = Range(ActiveCell.Address & "," & Selection.Address) _ .SpecialCells(xlCellTypeConstants) If myRange Is Nothing Then Exit Sub If Not myRange Is Nothing Then For Each cell In myRange myStr = cell.text For i = 1 To Len(myStr) If (Asc(UCase(Mid(myStr, i, 1))) < 48) Or _ (Asc(UCase(Mid(myStr, i, 1))) 57) Then myStr = Left(myStr, i - 1) & " " & Mid(myStr, i + 1)/1000 End If Next i cell.Value = Application.Trim(myStr) Next cell Selection.Replace What:=" ", Replacement:="", LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False End If With Application .Calculation = xlAutomatic .ScreenUpdating = True End With End Sub Gord Dibben XL2002 On Thu, 20 Nov 2003 15:43:41 -0600, jloberg wrote: Hey all, I've been reading thru tutorials and tips and forums for a few hours now and haven't found anything to help with my problem. I Am new to excel vba, yet believe my problem should be easy to solve with a macro. Here's the deal: I'm importing simple data-sets into Excel and find that each cell value includes a ' at the beginning of the value ... eg= ['150.234 in]. I would like to create a macro that edits the cell, so deleting the first character ['] and last three characters [ in]. If someone could point me in the right direction ... with an example macro, or where to find one for cell editing ... I'd appreciate it. thanks. loberg ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~View and post usenet messages directly from http://www.ExcelForum.com/ |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
editing cells...
Thanks, Ron! This was the simple solution I was looking for ... sor of. Get this: your macro returns [50.234] from ['150.234] .... but when remove the second cell.Value line in your macro I get a clea [150.234]. This will work sufficiently. But I'm curious why the ' is removed. Thank you for your help. loberg * Sub test() For Each cell In Selection On Error Resume Next cell.Value = Left(cell.Value, Len(cell.Value) - 3) On Error GoTo 0 Next End Sub * Ron de Bruin wrote: [b]Try something like this for the selection Sub test() For Each cell In Selection On Error Resume Next cell.Value = Left(cell.Value, Len(cell.Value) - 3) cell.Value = Right(cell.Value, Len(cell.Value) - 1) On Error GoTo 0 Next End Sub -- Regards Ron de Bruin (Win XP Pro SP-1 XL2002 SP-2) www.rondebruin.n ----------------------------------------------- ~~ Message posted from http://www.ExcelTip.com ~~View and post usenet messages directly from http://www.ExcelForum.com |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
editing cells...
Hey thanks, Gord! ...that definately kills the ' in my cells. Got a question tho: you say that this should strip all characters in cell except for numbers and the decimal point .... which is exactl what I really need -- I was looking to trim the cell.Value from eithe side, but you thought to just filter out unwanted characters from th string. Thanks for the insight. However- I found that your macro returns [150.234in] from ['150.234 in .... leaving the "in" ... so not filtering letters. <? I've studied your macro but I'm so green at this that I don't kno where to begin tweaking it. Thanks again, and in advance. loberg Gord Dibben wrote: [b]loberg This macro will strip all but numbers and the decimal point. Well, not quite.......it will also leave /(asc 47) if present. Yo don't have any of these /'s in your example so no problem<g Took about 15 seconds to convert 222,000 cells on 2.6Ghz machine Public Sub StripAllAZs() Dim myRange As Range Dim cell As Range Dim myStr As String Dim i As Integer With Application .ScreenUpdating = False .Calculation = xlManual End With On Error Resume Next Set myRange = Range(ActiveCell.Address & "," & Selection.Address) _ .SpecialCells(xlCellTypeConstants) If myRange Is Nothing Then Exit Sub If Not myRange Is Nothing Then For Each cell In myRange myStr = cell.text For i = 1 To Len(myStr) If (Asc(UCase(Mid(myStr, i, 1))) < 48) Or _ (Asc(UCase(Mid(myStr, i, 1))) 57) Then myStr = Left(myStr, i - 1) & " " & Mid(myStr, i + 1)/1000 End If Next i cell.Value = Application.Trim(myStr) Next cell Selection.Replace What:=" ", Replacement:="", LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False End If With Application .Calculation = xlAutomatic .ScreenUpdating = True End With End Sub Gord Dibben XL200 ----------------------------------------------- ~~ Message posted from http://www.ExcelTip.com ~~View and post usenet messages directly from http://www.ExcelForum.com |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
editing cells...
Then you only see the ' in the formulabar and not in the cell
Am I right? If you place a ' in front of a number for example Excel see it as text then. -- Regards Ron de Bruin (Win XP Pro SP-1 XL2002 SP-2) www.rondebruin.nl "jloberg" wrote in message ... Thanks, Ron! This was the simple solution I was looking for ... sort of. Get this: your macro returns [50.234] from ['150.234] .... but when I remove the second cell.Value line in your macro I get a clean [150.234]. This will work sufficiently. But I'm curious why the ' is removed. Thank you for your help. loberg * Sub test() For Each cell In Selection On Error Resume Next cell.Value = Left(cell.Value, Len(cell.Value) - 3) On Error GoTo 0 Next End Sub * Ron de Bruin wrote: [b]Try something like this for the selection Sub test() For Each cell In Selection On Error Resume Next cell.Value = Left(cell.Value, Len(cell.Value) - 3) cell.Value = Right(cell.Value, Len(cell.Value) - 1) On Error GoTo 0 Next End Sub -- Regards Ron de Bruin (Win XP Pro SP-1 XL2002 SP-2) www.rondebruin.nl ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~View and post usenet messages directly from http://www.ExcelForum.com/ |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
editing cells...
loberg
Apologies. I posted the wrong code. Was playing around with it and sent wrong copy. Alterations to make........ Change <48 to <46 If (Asc(UCase(Mid(myStr, i, 1))) < 48) Or _ Remove the /1000 from myStr = Left(myStr, i - 1) & " " & Mid(myStr, i + 1)/1000 Gord On Fri, 21 Nov 2003 09:19:16 -0600, jloberg wrote: Hey thanks, Gord! ...that definately kills the ' in my cells. Got a question tho: you say that this should strip all characters in a cell except for numbers and the decimal point .... which is exactly what I really need -- I was looking to trim the cell.Value from either side, but you thought to just filter out unwanted characters from the string. Thanks for the insight. However- I found that your macro returns [150.234in] from ['150.234 in] ... leaving the "in" ... so not filtering letters. <? I've studied your macro but I'm so green at this that I don't know where to begin tweaking it. Thanks again, and in advance. loberg Gord Dibben wrote: [b]loberg This macro will strip all but numbers and the decimal point. Well, not quite.......it will also leave /(asc 47) if present. You don't have any of these /'s in your example so no problem<g Took about 15 seconds to convert 222,000 cells on 2.6Ghz machine Public Sub StripAllAZs() Dim myRange As Range Dim cell As Range Dim myStr As String Dim i As Integer With Application .ScreenUpdating = False .Calculation = xlManual End With On Error Resume Next Set myRange = Range(ActiveCell.Address & "," & Selection.Address) _ .SpecialCells(xlCellTypeConstants) If myRange Is Nothing Then Exit Sub If Not myRange Is Nothing Then For Each cell In myRange myStr = cell.text For i = 1 To Len(myStr) If (Asc(UCase(Mid(myStr, i, 1))) < 48) Or _ (Asc(UCase(Mid(myStr, i, 1))) 57) Then myStr = Left(myStr, i - 1) & " " & Mid(myStr, i + 1)/1000 End If Next i cell.Value = Application.Trim(myStr) Next cell Selection.Replace What:=" ", Replacement:="", LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False End If With Application .Calculation = xlAutomatic .ScreenUpdating = True End With End Sub Gord Dibben XL2002 ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~View and post usenet messages directly from http://www.ExcelForum.com/ Gord Dibben XL2002 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
editing cells automatically??? | Excel Worksheet Functions | |||
editing liked cells | Excel Discussion (Misc queries) | |||
Need Help Editing Cells | New Users to Excel | |||
editing cells without using mouse | Excel Discussion (Misc queries) | |||
disable cells editing | Excel Discussion (Misc queries) |