Solved: Need to extract all info between ""

[jmenche]

Hi,

I imported a large text file (100k records x 12 fields) and every value in every field is in this format.

=T("<value>")

Doing a search and replace crashes my machine. I was hoping there was a subroutine that could loop through the fields and extract only the value part or get rid of the unwanted part.

Can anyone help a brother out??

Thanks!!

[Steiner]

Maybe this little function might help you, it will extract the part between the " in a String:

[VBA]
Public Function getVal(sText As String)
Dim iPos1%, iPos2%

iPos1 = InStr(sText, """")
iPos2 = InStr(iPos1 + 1, sText, """")

If iPos1 > 0 And iPos2 > 0 Then
getVal = Mid(sText, iPos1 + 1, iPos2 - iPos1 - 1)
Else
getVal = ""
End If
End Function
[/VBA]

Daniel

[jmenche]

Thanks Steiner!

I realized late last night that the the pattern was the same for each value so I used the mid function in a query for each field.

=mid(field,5,len(field)-6)

I'll try yours too.

[xCav8r]

Daniel, if that doesn't already exist in the KB, this could be a good entry. A few suggestions...

[VBA]' Type the function; name should be more descriptive;
' qualifier should be made into param
Public Function getVal(sText As String)
Dim iPos1%, iPos2% 'type the variables as integers (your prefixed them!!!)

iPos1 = InStr(sText, """")
iPos2 = InStr(iPos1 + 1, sText, """")

If iPos1 > 0 And iPos2 > 0 Then
getVal = Mid(sText, iPos1 + 1, iPos2 - iPos1 - 1)
Else
getVal = ""
End If
End Function [/VBA]

Questions:

1. What's the value in abbreviating Position to Pos when sharing code?
2. What if I'm dealing with a string that has more than one string enclosed by double quotation marks?

[Steiner]

Hi xCav8r,

thanks for your input. I just searched the KB but not find such an entry, so maybe I'll make one. To your questions:

1. Heh, the function name was quite descriptive (at least compared to my usual "test") . And I'm not sure how to name the function properly (descriptive AND short), especially when the delimiter is a parameter.

2. The variables are declared as integer (the % does this, in a KB entry I could use the long declaration ). And I used Pos because everybody should know that this means Position (you knew it, too), it's less to type and keeps the lines shorter (and it's some kind of standard name here in our company).

3. About text with more than one pair of ": This function just returns the text between the first pair. To get more, the result had to be an array. And with that functionality we are almost there at the Split-function (which I simply did not use because Excel97 does not know it). More problems arise, when there is an odd number of ", where is the text? I'm not sure whether a generic function could handle this reliably.

Maybe we should discuss this a bit more before I create a KB entry of that one, so we don't have the discussion when you have to approve it .

Daniel

[xCav8r]

Daniel,

1. Heh, the function name was quite descriptive (at least compared to my usual "test") . And I'm not sure how to name the function properly (descriptive AND short), especially when the delimiter is a parameter.

How about GetStringFromQuotation?

2. The variables are declared as integer (the % does this, in a KB entry I could use the long declaration ). And I used Pos because everybody should know that this means Position (you knew it, too), it's less to type and keeps the lines shorter (and it's some kind of standard name here in our company).

I understand the preference for brevity when typing, but when sharing code with others, wouldn't it be better to assume as little knowledge as possible from those who are asking the questions? Knowing % = integer and Pos = Position, I think, are unnecessary assumptions. Longer declarations and longer variable names only make your code more user-friendly.

This isn't to suggest that you change the way you code. Consider this nothing more than gentle encouragement from some random internet guy about the way you should share your code in a learning/teaching environment.

3. About text with more than one pair of ": This function just returns the text between the first pair. To get more, the result had to be an array. And with that functionality we are almost there at the Split-function (which I simply did not use because Excel97 does not know it). More problems arise, when there is an odd number of ", where is the text? I'm not sure whether a generic function could handle this reliably.

I think that either would pass for a decent KB entry, though obviously the latter would be more useful--even if it requires 2000 and up. Choice is yours.

Maybe we should discuss this a bit more before I create a KB entry of that one, so we don't have the discussion when you have to approve it .

I'm happy to discuss it in as much detail as you'd like. Just keep in mind that as a KB approver, I follow the guidance of VBAX for approving submissions, and some of what I've posted in this thread is taken more from my own opinion. Using longer variable names, for example, is not a requirement for KB submissions. It's just something that I think you should do when sharing.