Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Removing Text
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Removing Text
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 Tubthumper wrote: 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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Removing Text
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 Tubthumper wrote: 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 -- Dave Peterson |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Removing Text
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.) -- Kevin Vaughn "Tubthumper" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Text File creates incorrect Date format | Excel Discussion (Misc queries) | |||
Find & Replace text format | Excel Discussion (Misc queries) | |||
Removing text characters | Excel Worksheet Functions | |||
Read Text File into Excel Using VBA | Excel Discussion (Misc queries) |