extracting specific info from text cell.
I have a data file that has a column of data with entry inputs that are not
the same. For instance: Range 4-113 might be in one cell, and 4-110 Row 3 in a following cell. I want to extract into a new column the "4-110" type information. I can't use len, find, left, mid combination because it will work for the cell containing "Range 4-113", but not for the one "4-110 Row 3." There are 9000 rows of data and I don't want to sort through the different combinations and create formulas for each. So my thinking is either a complex formula that is eluding me, or possibly some VBA code. I am a novice VBA user so kind of stuck. Since this data is automated and updated from time-to-time, I was hoping for something that I could use going forward. Thanks for any help that can be provided! -- Thanks, Chip |
extracting specific info from text cell.
Is your text **always** a single digit followed by a dash followed by a
three digit number as your examples show? If not, what are the limits of the numbers on each side of the dash? Also, will there ever be another dash in your text besides the one between the two numbers you are looking to extract? -- Rick (MVP - Excel) "Chipgiii" wrote in message ... I have a data file that has a column of data with entry inputs that are not the same. For instance: Range 4-113 might be in one cell, and 4-110 Row 3 in a following cell. I want to extract into a new column the "4-110" type information. I can't use len, find, left, mid combination because it will work for the cell containing "Range 4-113", but not for the one "4-110 Row 3." There are 9000 rows of data and I don't want to sort through the different combinations and create formulas for each. So my thinking is either a complex formula that is eluding me, or possibly some VBA code. I am a novice VBA user so kind of stuck. Since this data is automated and updated from time-to-time, I was hoping for something that I could use going forward. Thanks for any help that can be provided! -- Thanks, Chip |
extracting specific info from text cell.
Can you give more examples of the data you have? And more examples of the
data you want to extract? What are the common criteria of the data you want? Is the format always #-###? Need more specifics before I begin to think. -- Cheers, Ryan "Chipgiii" wrote: I have a data file that has a column of data with entry inputs that are not the same. For instance: Range 4-113 might be in one cell, and 4-110 Row 3 in a following cell. I want to extract into a new column the "4-110" type information. I can't use len, find, left, mid combination because it will work for the cell containing "Range 4-113", but not for the one "4-110 Row 3." There are 9000 rows of data and I don't want to sort through the different combinations and create formulas for each. So my thinking is either a complex formula that is eluding me, or possibly some VBA code. I am a novice VBA user so kind of stuck. Since this data is automated and updated from time-to-time, I was hoping for something that I could use going forward. Thanks for any help that can be provided! -- Thanks, Chip |
extracting specific info from text cell.
Actually, as long as there is never another dash in your text besides the
one between the numbers you are looking for, this should work for you... Function ExtractText(S As String) As String Dim Parts() As String, SubParts() As String Parts = Split(S, "-") SubParts = Split(Parts(0), " ") ExtractText = SubParts(UBound(SubParts)) & "-" SubParts = Split(Parts(1), " ") ExtractText = ExtractText & SubParts(0) End Function -- Rick (MVP - Excel) "Rick Rothstein" wrote in message ... Is your text **always** a single digit followed by a dash followed by a three digit number as your examples show? If not, what are the limits of the numbers on each side of the dash? Also, will there ever be another dash in your text besides the one between the two numbers you are looking to extract? -- Rick (MVP - Excel) "Chipgiii" wrote in message ... I have a data file that has a column of data with entry inputs that are not the same. For instance: Range 4-113 might be in one cell, and 4-110 Row 3 in a following cell. I want to extract into a new column the "4-110" type information. I can't use len, find, left, mid combination because it will work for the cell containing "Range 4-113", but not for the one "4-110 Row 3." There are 9000 rows of data and I don't want to sort through the different combinations and create formulas for each. So my thinking is either a complex formula that is eluding me, or possibly some VBA code. I am a novice VBA user so kind of stuck. Since this data is automated and updated from time-to-time, I was hoping for something that I could use going forward. Thanks for any help that can be provided! -- Thanks, Chip |
All times are GMT +1. The time now is 01:24 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com