"formula is too long" AND test for whether double-quotes are next-to text or number??
Barb,
Try this
Sub RemoveQuotes()
Dim oCell As Range
Dim sFirst As String
Application.ScreenUpdating = False
With Worksheets(1).Columns(1)
Set oCell = .Find("""", LookIn:=xlValues)
If Not oCell Is Nothing Then
CheckValue oCell
Do
Set oCell = .FindNext(oCell)
If Not oCell Is Nothing Then
CheckValue oCell
End If
Loop While Not oCell Is Nothing
End If
End With
Application.ScreenUpdating = True
End Sub
Private Sub CheckValue(cell As Range)
Dim iPos As Long
iPos = InStr(cell.Value, """")
If iPos = 1 Then 'just remove
cell.Value = Replace(cell.Value, """", "")
ElseIf IsNumeric(cell.Characters(iPos - 1, 1).Text) Then
cell.Value = Replace(cell.Value, """", "in")
Else
cell.Value = Replace(cell.Value, """", "'")
End If
End Sub
--
HTH
Bob Phillips
(replace somewhere in email address with gmail if mailing direct)
"The Moose" wrote in message
oups.com...
I've got an Excel spreadsheet that's got thousands of products that I
want to select from to bulk-import to my store.
The problem(s) I have is that the data is real sloppy. Lot of typos.
Double-quotes which prevent sorting. Double-quotes which mess up the
bulk-import.
I'm getting "formula too long" on MANY-MANY of the description cells.
I can't spell-check. I can't bulk-replace the dang double-quotes.
I cannot figure out how to get past that "formula too long" error
unless I output to a text file and then bring it back into Excel when
I'm done.
I'd like to get rid of the double-quotes. I know part of the problem
is that I'm outputting to a TAB file to do the bulk-import to my store
-- that's putting double-double-quotes into the text. I cannot use the
comma-delimited file because there are commas in the text and that gets
messed up even worse than the TAB file.
So...my questions:
Is there a way to do the following:
(1) If double-quotes are next to text -- change to single-quote??
(2) If double-quotes are next to numbers (0-9) -- change to measurement
( in.)??
(3) How can I get past the "formula is too long" error so I can
spell-check this crap??
Thanks.
Barb
|