|
|
Our Hot Pick: Rising Antivirus 2006 - Certified by TUV & Checkmark! Get 10% discount by entering this coupon code: ONDISCOUNT10
Hello,
Is there a way for excel to extract a snippet of data from a text string?
For the following string,
Form:FOUR:OL_FRYEN:00:Text:ON
I want to extract the data between the 2nd and 3rd colons, so the result
should be:
OL_FRYEN
The data is always in Column A, so ideally, the extracted bit would be
entered beside the source string, in Column B.
Each tab (268 of them) in my spreadsheet/workbook has anywhere between 30 to
6000 lines, and I want to extract the data between the 2nd and 3rd ":" in
each case.
I started to do this using a formula but it quickly got very cumbersome.
Can a Macro do this? Or is there a formula I can use?
Thank you for your assistance,
Art.
|
|
Is there *always* at least 3 colons in each string?
Have you considered using Text to Columns which will parse the string into
segments based on the colon as a delimiter?
Biff
"Art MacNeil" <artmacneil[ at ]shaw.ca> wrote in message
news:IVmTg.68857$1T2.1855[ at ]pd7urf2no...
[Quoted Text] > Hello,
>
> Is there a way for excel to extract a snippet of data from a text string?
> For the following string,
>
> Form:FOUR:OL_FRYEN:00:Text:ON
>
> I want to extract the data between the 2nd and 3rd colons, so the result
> should be:
>
> OL_FRYEN
>
> The data is always in Column A, so ideally, the extracted bit would be
> entered beside the source string, in Column B.
>
> Each tab (268 of them) in my spreadsheet/workbook has anywhere between 30
> to 6000 lines, and I want to extract the data between the 2nd and 3rd ":"
> in each case.
>
> I started to do this using a formula but it quickly got very cumbersome.
> Can a Macro do this? Or is there a formula I can use?
>
> Thank you for your assistance,
>
> Art.
>
>
>
>
>
>
>
|
|
On Sat, 30 Sep 2006 05:04:40 GMT, "Art MacNeil" <artmacneil[ at ]shaw.ca> wrote:
[Quoted Text] >Hello,
>
> Is there a way for excel to extract a snippet of data from a text string?
>For the following string,
>
>Form:FOUR:OL_FRYEN:00:Text:ON
>
>I want to extract the data between the 2nd and 3rd colons, so the result
>should be:
>
>OL_FRYEN
>
>The data is always in Column A, so ideally, the extracted bit would be
>entered beside the source string, in Column B.
>
>Each tab (268 of them) in my spreadsheet/workbook has anywhere between 30 to
>6000 lines, and I want to extract the data between the 2nd and 3rd ":" in
>each case.
>
>I started to do this using a formula but it quickly got very cumbersome.
>Can a Macro do this? Or is there a formula I can use?
>
>Thank you for your assistance,
>
>Art.
>
>
>
>
>
>
Several methods:
It can certainly be done with a formula using built-ins:
=MID(A1,FIND(CHAR(1),SUBSTITUTE(
A1,":",CHAR(1),2))+1,FIND(CHAR(1),
SUBSTITUTE(A1,":",CHAR(1),3))-FIND(
CHAR(1),SUBSTITUTE(A1,":",CHAR(1),2))-1)
You can use a simpler formula if you download and install Longre's free
morefunc.xll add-in from http://xcell05.free.fr/ and then use the Regular
Expression formula:
=REGEX.MID(A1,"[^:]+",3)
You'll also get a bunch of other useful functions, which can be embedded in
your worksheet if it is to be distributed.
Finally, you can select Data/Text to Columns and use ":" as the delimiter.
Then delete the non-relevant columns. This latter could be recorded as a
macro.
--ron
|
|
Yes there is always at least 3 colons in each string, and no (D'oh) I had
not considered importing the text file and then parsing it with the colon as
the delimiter.
I have more than 250 text files to do so that seems like a lot of work.
Thanks for the suggestion though,
Art.
"Biff" <biffinpitt[ at ]comcast.net> wrote in message
news:OYsPFHF5GHA.932[ at ]TK2MSFTNGP04.phx.gbl...
[Quoted Text] > Is there *always* at least 3 colons in each string? > > Have you considered using Text to Columns which will parse the string into > segments based on the colon as a delimiter? > > Biff > > "Art MacNeil" <artmacneil[ at ]shaw.ca> wrote in message > news:IVmTg.68857$1T2.1855[ at ]pd7urf2no... >> Hello, >> >> Is there a way for excel to extract a snippet of data from a text >> string? For the following string, >> >> Form:FOUR:OL_FRYEN:00:Text:ON >> >> I want to extract the data between the 2nd and 3rd colons, so the result >> should be: >> >> OL_FRYEN >> >> The data is always in Column A, so ideally, the extracted bit would be >> entered beside the source string, in Column B. >> >> Each tab (268 of them) in my spreadsheet/workbook has anywhere between 30 >> to 6000 lines, and I want to extract the data between the 2nd and 3rd ":" >> in each case. >> >> I started to do this using a formula but it quickly got very cumbersome. >> Can a Macro do this? Or is there a formula I can use? >> >> Thank you for your assistance, >> >> Art. >> >> >> >> >> >> >> > >
|
|
"Ron Rosenfeld" <ronrosenfeld[ at ]nospam.org> wrote in message
news:s8ksh2teaa0rjdlp02fll9cv2ok9e5msj2[ at ]4ax.com...
[Quoted Text] > On Sat, 30 Sep 2006 05:04:40 GMT, "Art MacNeil" <artmacneil[ at ]shaw.ca> > wrote: > >>Hello, >> >> Is there a way for excel to extract a snippet of data from a text >> string? >>For the following string, >> >>Form:FOUR:OL_FRYEN:00:Text:ON >> >>I want to extract the data between the 2nd and 3rd colons, so the result >>should be: >> >>OL_FRYEN >> >>The data is always in Column A, so ideally, the extracted bit would be >>entered beside the source string, in Column B. >> >>Each tab (268 of them) in my spreadsheet/workbook has anywhere between 30 >>to >>6000 lines, and I want to extract the data between the 2nd and 3rd ":" in >>each case. >> >>I started to do this using a formula but it quickly got very cumbersome. >>Can a Macro do this? Or is there a formula I can use? >> >>Thank you for your assistance, >> >>Art. >> >> >> >> >> >> > > Several methods: > > It can certainly be done with a formula using built-ins: > > =MID(A1,FIND(CHAR(1),SUBSTITUTE( > A1,":",CHAR(1),2))+1,FIND(CHAR(1), > SUBSTITUTE(A1,":",CHAR(1),3))-FIND( > CHAR(1),SUBSTITUTE(A1,":",CHAR(1),2))-1) > > You can use a simpler formula if you download and install Longre's free > morefunc.xll add-in from http://xcell05.free.fr/ and then use the Regular > Expression formula: > > =REGEX.MID(A1,"[^:]+",3) > > You'll also get a bunch of other useful functions, which can be embedded > in > your worksheet if it is to be distributed. > > Finally, you can select Data/Text to Columns and use ":" as the delimiter. > Then delete the non-relevant columns. This latter could be recorded as a > macro. > > > --ron
Yippee, =MID(A1,FIND(CHAR...) did the trick. I've downloaded the
morefunc.exe as well.
Thanks you,
Art.
|
|
Here's another formula using the built-in functions that's several
keystrokes shorter:
=LEFT(MID(A1,FIND("~",SUBSTITUTE(A1,":","~",2))+1,255),FIND(":",MID(A1,FIND("~",SUBSTITUTE(A1,":","~",2))+1,255))-1)
Biff
"Art MacNeil" <artmacneil[ at ]shaw.ca> wrote in message
news:KiwTg.70335$5R2.33784[ at ]pd7urf3no...
[Quoted Text] > Yes there is always at least 3 colons in each string, and no (D'oh) I had > not considered importing the text file and then parsing it with the colon > as the delimiter. > > I have more than 250 text files to do so that seems like a lot of work. > > Thanks for the suggestion though, > > Art. > > > "Biff" <biffinpitt[ at ]comcast.net> wrote in message > news:OYsPFHF5GHA.932[ at ]TK2MSFTNGP04.phx.gbl... >> Is there *always* at least 3 colons in each string? >> >> Have you considered using Text to Columns which will parse the string >> into segments based on the colon as a delimiter? >> >> Biff >> >> "Art MacNeil" <artmacneil[ at ]shaw.ca> wrote in message >> news:IVmTg.68857$1T2.1855[ at ]pd7urf2no... >>> Hello, >>> >>> Is there a way for excel to extract a snippet of data from a text >>> string? For the following string, >>> >>> Form:FOUR:OL_FRYEN:00:Text:ON >>> >>> I want to extract the data between the 2nd and 3rd colons, so the result >>> should be: >>> >>> OL_FRYEN >>> >>> The data is always in Column A, so ideally, the extracted bit would be >>> entered beside the source string, in Column B. >>> >>> Each tab (268 of them) in my spreadsheet/workbook has anywhere between >>> 30 to 6000 lines, and I want to extract the data between the 2nd and 3rd >>> ":" in each case. >>> >>> I started to do this using a formula but it quickly got very cumbersome. >>> Can a Macro do this? Or is there a formula I can use? >>> >>> Thank you for your assistance, >>> >>> Art. >>> >>> >>> >>> >>> >>> >>> >> >> > >
|
|
|