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

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" <Chipgiii@discussions.microsoft.com> wrote in message 
news:ADBD0186-6213-436A-BC0B-1FF61347B9B3@microsoft.com...
>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 

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

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" <rick.newsNO.SPAM@NO.SPAMverizon.net> wrote in message 
news:eY4HEbrnKHA.5344@TK2MSFTNGP04.phx.gbl...
> 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" <Chipgiii@discussions.microsoft.com> wrote in message 
> news:ADBD0186-6213-436A-BC0B-1FF61347B9B3@microsoft.com...
>>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
>