Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
"formula is too long" AND test for whether double-quotes are next-to text or number??
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
"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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
"formula is too long" AND test for whether double-quotes are next-to text or number??
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 Bob Phillips wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Stop excel from dropping the 0 in the beginning of a number? | Setting up and Configuration of Excel | |||
Text entries behaving like numbers | Excel Discussion (Misc queries) | |||
Cell text based on 4 condition test | Excel Worksheet Functions | |||
Calculating p-value from Fisher's Exact Test | Excel Worksheet Functions | |||
Find all text instances in a sheet and add one number from each row | Excel Discussion (Misc queries) |