"formula is too long" AND test for whether double-quotes are next-to text or number??

[The Moose]

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

 

[Bob Phillips]

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]

I want to thank both of you. Both these methods work incredibly.

I've been working on spell-checking this KRAP. Just found out after I
finished that the creator of the DB is purchasing a spell-checker
(somebody heard my prayers OR my cursing) :GRIN:

I appreciate all your help.

The next go-round shouldn't be so time-intensive now that visions of
spell-checkers are dancing in my head.

Thanks.

Barb