Extracting text from a string.

[Art MacNeil]

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.

 

[Biff]

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

 

[Ron Rosenfeld]

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

 

[Art MacNeil]

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.

 

[Art MacNeil]

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.

 

[Biff]

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

 

[Ron Rosenfeld]

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.

You're welcome. Glad to help. Thanks for the feedback.
--ron