Removing Text

T

Tubthumper

I have a long list of URLs, and I'd like to extract just the information
between the "speech marks". Any suggestions?

<option value="ohwell.info">ohwell.info
<option value="thewebtunnel.com">thewebtunnel.com
<option value="shipproxy.com">shipproxy.com
<option value="filtercloak.com">filtercloak.com
<option value="brownman.info">brownman.info

Many thanks
 
L

Lori

Select the data Column, A say, and choose Data > Text to Columns with
the options:

Step 1. Delimited
Step 2. Other delimiter and type "
Step 3. Choose do not import for first and third columns, and
destination as $B$1
 
D

Dave Peterson

One way:

Select the range to fix
edit|Replace
what: *=" (asterisk, equal sign, doublequote)
with: (leave blank)
replace all

then with that range still selected
edit|Replace
what: "* (doublequote, asterisk)
with: (leave blank)
replace all
 
K

Kevin Vaughn

Since the other replies (that I saw) were not formulaic in nature, I thought
I would try my hand at it. First, the formula that I created, that appears
to work and that prior to a couple of days ago, I would have shown and let
that be the end of it. But after reading an interesting discussion on the
daily dose of Excel by Tushar Mehta, I will also offer a different and easier
to understand solution:

=MID(A1,FIND(CHAR(34),A1)+1,LEN(A1)-FIND(CHAR(34),A1,FIND(CHAR(34),A1)+1)-1)

However, as the above formula is no doubt difficult to understand for most,
it would be better to use some helper columns with the following formulas:

=FIND(CHAR(34),A7)
=FIND(CHAR(34), A7, B7 + 1)
=LEN(A7)
=MID(A7,B7 + 1,D7 - C7 - 1)

Note, I did no error checking, but it appeared to work on the provided test
data. After I did the 2nd set of formulas, I noticed that the first double
quote (or what I believe you called the speech mark) was always at position
16, so you could replace the first formula, but then it would not be as
robust. BTW, it took me a few minutes to figure out how to determine that
the speech mark was char(34) as I do not have all Excel functions memorized
(as some on this board appear to.)
 
Top