Text string extraction

[Geoffric]

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.

 

[Ron Coderre]

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)

 

[Gary''s Student]

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

 

[JE McGimpsey]

One way:

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

 

[Teethless mama]

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

 

[Harlan Grove]

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.