parsing data

S

Sam Hodo

Hello;

I have two data string types that I am doing a search on.

ex:1
"""ACCESS, NON-STANDARD SET-UP CLEAN"""

I would like to strip all of the quotes from the front and then the same
number of quotes from the back of the string so that it looks like

ACCESS, NON-STANDARD SET-UP CLEAN

How can I do this, since the number of quotes may vary between search string.

also
ex:2
"""FITTING ALLOW 0.75""""/EA"""

Note that in ex:2 should look like
FITTING ALLOW 0.75" /EA

Thanks for your time and effort in this...
 
D

Dave Peterson

Is a macro ok?

If yes:

Option Explicit
Sub testme()
Dim myRng As Range
Dim myCell As Range
Dim HowMany As Long
Dim iCtr As Long

Set myRng = Selection

For Each myCell In myRng.Cells
For iCtr = 1 To Len(myCell.Value)
If Mid(myCell.Value, iCtr, 1) <> Chr(34) Then
HowMany = iCtr - 1
Exit For
End If
Next iCtr
myCell.Value = Replace(myCell.Value, String(HowMany, Chr(34)), "")
Next myCell

End Sub

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Replace was added in xl2k. If you're using xl97, you can change Replace() to
application.substitute().
 
R

Ron Coderre

Try this:

Select the range of cells to be impacted.

From the Excel main menu:
<edit><replace>
Find what: """.....that's 3 quotes
Replace with: (leave this blank)
Click the [replace all] button

That will remove triple quotes, but where there are 4, 1 will be left
remaining.

Does that help?
***********
Regards,
Ron

XL2002, WinXP
 
J

JE McGimpsey

Your description and example are somewhat ambiguous, since the example
shows a change in quotes in the middle of the string, as well as in
front and in back, and the middle substitution appears to add a space
character. Just based on the example:

Also, are you trying to do this one-time using Find/Replace (ignoring
the space character)?

Find: """
Replace: <blank>

or a formula (again ignoring the space character):

A1: """FITTING ALLOW 0.75""""/EA"""
B1: =SUBSTITUTE(A1,"""""""","")
 
R

Ron Rosenfeld

Hello;

I have two data string types that I am doing a search on.

ex:1
"""ACCESS, NON-STANDARD SET-UP CLEAN"""

I would like to strip all of the quotes from the front and then the same
number of quotes from the back of the string so that it looks like

ACCESS, NON-STANDARD SET-UP CLEAN

How can I do this, since the number of quotes may vary between search string.

also
ex:2
"""FITTING ALLOW 0.75""""/EA"""

Note that in ex:2 should look like
FITTING ALLOW 0.75" /EA

Thanks for your time and effort in this...
Click to expand...

Here's another solution using Regular Expressions. This uses the number of
quote marks at the beginning as the "key" to determine how long subsequent
strings should be. It also includes the <space> after the internal, single
quote. But it will strip out any double spaces within the string. If that
will be a problem, the formula can be modified.

First, download and install Longre's free morefunc.xll add-in from
http://xcell05.free.fr/

Then use this formula:

=TRIM(REGEX.SUBSTITUTE(A1,"""{"&LEN(REGEX.MID(A1,"^""*"))&"}(?=[^""]|$)"," "))
--ron
 
Top