VBA help removing unwanted quote marks please

  Newuser3443 15:06 20 Oct 2005
Locked

I have imported a text file in to Excel. Unfortunately some of the text fields include " marks within the text (I can not change the text file), so I can't use " as a text identifier.

The file is VERY large and Search/Replace would take ages (if Excel doesn't fall over in the meantime).

I guess I need to use SUBSTITUTE (A1,CHAR(34),"") but how do apply this to the current cell without creating another column to store the result in?

Sorry, I'm probably not explaing this very well!

Rob :(

  VoG II 15:55 20 Oct 2005

Sub NoQuote()

Dim cell As Range

For Each cell In Selection

If Not IsEmpty(cell) Then ExtractValue cell

Next

End Sub

Sub ExtractValue(anyCell As Range)

Dim s As String

Dim N As Integer, I As Integer

s = anyCell.Value

N = InStr(s, Chr(34))

While N > 0

I = I + 1

anyCell.Value = Left(s, N - 1)

s = Mid(s, N + 1)

N = InStr(s, Chr(34))

Wend

I = I + 1

anyCell.Value = anyCell.Value & s

End Sub

  Newuser3443 16:03 20 Oct 2005

To the rescue again :)

Stuck though. I import each line of the text file complete with all of its quote marks and commas and then delimit each line.

Each line is made up of 20 fields with each field being contained in " " marks.

Some of the fields are just "" (i.e. empty).

Others have a " within them (usually to represent inches).

If each text line has 20 fields and I have say 30000 lines, it is possible to modify your above coding to show me what I'd need to do please?

Sorry again for my ignorance VoG.

  Newuser3443 16:05 20 Oct 2005

i.e. each line of 20 fields is imported as 1 complete string before delimiting it (using the LINEINPUT command)

  VoG II 16:07 20 Oct 2005

Sub NoQuote()

Dim cell As Range

For Each cell In Selection

If Not IsEmpty(cell) Then ExtractValue cell

Next

End Sub

Sub ExtractValue(anyCell As Range)

Dim s As String

Dim N As Integer

s = anyCell.Value

N = InStr(s, Chr(34))

If N = 0 Then Exit Sub

anyCell.Value = ""

While N > 0

anyCell.Value = anyCell.Value & Left(s, N - 1)

s = Mid(s, N + 1)

N = InStr(s, Chr(34))

Wend

anyCell.Value = anyCell.Value & s

End Sub

  VoG II 16:09 20 Oct 2005

Sorry, I'm lost.

My code (the second lot) will remove all " in the selection. Is that not what you wanted?

  Newuser3443 16:12 20 Oct 2005

Absolutely superb, that's exactly that I want.

Thank you so much, you have my admiration.

That's going to save me a lot of heartache.

Rob ;)

  Monoux 16:33 20 Oct 2005

bookmark

This thread is now locked and can not be replied to.

Elsewhere on IDG sites

AMD Radeon Adrenalin release date, new features, compatible graphics cards

8 brilliant character artists speaking at Pictoplasma 2018

iMac Pro release date, UK price & specs

Football : comment regarder la Ligue 1 en direct ?