editing cells...

  • Thread starter Thread starter jloberg
  • Start date Start date
J

jloberg

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


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
Click to expand...
 
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 said:
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
Click to expand...
 
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)




jloberg said:
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
*


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
Click to expand...
Click to expand...
 
loberg

Apologies.

I posted the wrong code. Was playing around with it and sent wrong copy.

Alterations to make........

Change <48 to <46
Remove the /1000 from
myStr = Left(myStr, i - 1) & " " & Mid(myStr, i + 1)/1000
Click to expand...
Gord




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 said:
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
Click to expand...
Click to expand...


Gord Dibben XL2002
 
Back
Top