Text string extraction
 

Can anyone suggest a €œone-stop€� formula to extract partial data from a
string. I can do it in perhaps two/three steps, but I havent fathomed how to
do it in one step. If the following data is in column A, from A2 down€¦.:

IC.0.0.863259.0.0.0
IC.0.Z28002.163119.0.0.0
IC.ITME.G80101.567149.0.0.0
IC.ITSE.0.769348.0.0.0
IC.SSSKP.G90312.567149.0.0.0

€¦.I would like to extract the information between the first full stop and
the third full stop, so that the result in column B2 down is as follows:

0.0
0.Z28002
ITME.G80101
ITSE.0
SSSKP.G90312

The difficulty I am facing is that the element after the first full stop can
be any length from 1 character to 5 characters, and the element after the
second full stop can be up to 6 characters in length.

Thanks in advance for any help you can give.

--
Geoff

Text string extraction
 

If the desired text always begins with the 4th character...

Try this:
=MID(A1,4,SEARCH("|",SUBSTITUTE(A1,".","|",3))-4)

But, if the prefix length may vary....
=MID(A1,FIND(".",A1)+1,FIND("|",SUBSTITUTE(A1,".", "|",3))-FIND(".",A1)-1)

Does that help?
--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)

"Geoffric" wrote in message
...
Can anyone suggest a "one-stop" formula to extract partial data from a
string. I can do it in perhaps two/three steps, but I haven't fathomed how
to
do it in one step. If the following data is in column A, from A2 down..:

IC.0.0.863259.0.0.0
IC.0.Z28002.163119.0.0.0
IC.ITME.G80101.567149.0.0.0
IC.ITSE.0.769348.0.0.0
IC.SSSKP.G90312.567149.0.0.0

..I would like to extract the information between the first full stop and
the third full stop, so that the result in column B2 down is as follows:

0.0
0.Z28002
ITME.G80101
ITSE.0
SSSKP.G90312

The difficulty I am facing is that the element after the first full stop
can
be any length from 1 character to 5 characters, and the element after the
second full stop can be up to 6 characters in length.

Thanks in advance for any help you can give.

--
Geoff

Text string extraction
 

Try this UDF:

Function first_to_third(r As Range) As String
s = Split(r.Value, ".")
first_to_third = s(1) & "." & s(2)
End Function

--
Gary''s Student - gsnu200761


"Geoffric" wrote:

Can anyone suggest a €œone-stop€� formula to extract partial data from a
string. I can do it in perhaps two/three steps, but I havent fathomed how to
do it in one step. If the following data is in column A, from A2 down€¦.:

IC.0.0.863259.0.0.0
IC.0.Z28002.163119.0.0.0
IC.ITME.G80101.567149.0.0.0
IC.ITSE.0.769348.0.0.0
IC.SSSKP.G90312.567149.0.0.0

€¦.I would like to extract the information between the first full stop and
the third full stop, so that the result in column B2 down is as follows:

0.0
0.Z28002
ITME.G80101
ITSE.0
SSSKP.G90312

The difficulty I am facing is that the element after the first full stop can
be any length from 1 character to 5 characters, and the element after the
second full stop can be up to 6 characters in length.

Thanks in advance for any help you can give.

--
Geoff

Text string extraction
 

One way:

=MID(LEFT(A1,FIND("$",SUBSTITUTE(A1,".","$",3))-1),FIND(".",A1)+1,255)


In article ,
Geoffric wrote:

Can anyone suggest a €œone-stop€� formula to extract partial data from a
string. I can do it in perhaps two/three steps, but I havent fathomed how to
do it in one step. If the following data is in column A, from A2 down€¦.:

IC.0.0.863259.0.0.0
IC.0.Z28002.163119.0.0.0
IC.ITME.G80101.567149.0.0.0
IC.ITSE.0.769348.0.0.0
IC.SSSKP.G90312.567149.0.0.0

€¦.I would like to extract the information between the first full stop and
the third full stop, so that the result in column B2 down is as follows:

0.0
0.Z28002
ITME.G80101
ITSE.0
SSSKP.G90312

The difficulty I am facing is that the element after the first full stop can
be any length from 1 character to 5 characters, and the element after the
second full stop can be up to 6 characters in length.

Thanks in advance for any help you can give.

Text string extraction
 

=MID(A1,FIND(".",A1)+1,FIND(".",A1,FIND(".",A1,FIN D(".",A1)+1)+1)-FIND(".",A1)-1)


"Geoffric" wrote:

Can anyone suggest a €œone-stop€� formula to extract partial data from a
string. I can do it in perhaps two/three steps, but I havent fathomed how to
do it in one step. If the following data is in column A, from A2 down€¦.:

IC.0.0.863259.0.0.0
IC.0.Z28002.163119.0.0.0
IC.ITME.G80101.567149.0.0.0
IC.ITSE.0.769348.0.0.0
IC.SSSKP.G90312.567149.0.0.0

€¦.I would like to extract the information between the first full stop and
the third full stop, so that the result in column B2 down is as follows:

0.0
0.Z28002
ITME.G80101
ITSE.0
SSSKP.G90312

The difficulty I am facing is that the element after the first full stop can
be any length from 1 character to 5 characters, and the element after the
second full stop can be up to 6 characters in length.

Thanks in advance for any help you can give.

--
Geoff

Text string extraction
 

Gary''s Student wrote...
Try this UDF:

Function first_to_third(r As Range) As String
s = Split(r.Value, ".")
first_to_third = s(1) & "." & s(2)
End Function
....

OP didn't mention which Excel version he's using. Your udf would fail
in Excel 97 and prior under Windows and all Mac versions of Excel
because the Split function was introduced in VBA 6.