editing cells...

[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

 

[Ron de Bruin]

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

 

[Gord Dibben]

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

 

[jloberg]

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

 

[jloberg]

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

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

 

[Ron de Bruin]

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)

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

 

[Gord Dibben]

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

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

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

Gord Dibben XL2002