extracting specific info from text cell.

  • Thread starter Thread starter Chipgiii
  • Start date Start date
C

Chipgiii

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!
 
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?
 
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.
 
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 said:
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?
Click to expand...
 
Back
Top