Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Help with Text import and data extraction | Excel Discussion (Misc queries) | |||
Sumif text is contained winthin a longer text string in a cell | Excel Worksheet Functions | |||
Complicated extraction of text | Excel Discussion (Misc queries) | |||
Splitting a text string into string and number | Excel Discussion (Misc queries) | |||
String Extraction... | Excel Worksheet Functions |