Hey all -
I'm trying to extract a number from a text string. The text string
varies in length and contents. The only thing that uniquely identifies the
data I need to extract is its format - specifically: The number is always 4
digits separated by a dash followed by 4 more digits. For example: 1234-1234.

The trick is that there are other numbers in there that come close to the
same format (ex: 12-1234).

Any suggestions?

Thanks in advance!
-meat

A user defined function (UDF) like the one below might work for you - it's
not complete or foolproof, but is a good basis for one.

To use it, first put it into your workbook:
Open the workbook. Press [Alt]+[F11] to enter the VB Editor and in the VBE,
choose Insert | Module and copy and paste the code below into the empty code
module presented to you. Close the VB Editor.

To use it in a worksheet, enter a formula such as
=GetNumGroup(A5)
where A5 is the cell containing the text you want to find the number group
in. So, if
A5 has "hello 1234-9876" in it, whatever cell you enter the =GetNumGroup(A5)
should display "1234-9876".
If no 4-4 group is found, the cell with the formula will remain blank.

Hope this helps a bit.

The code:

Function GetNumGroup(whatCell) As String
'only works properly if there's just one
'group of possible digits.
'Examples:
' hello 1234-5678 goodbye
'would be ok and found, but
' hello 1234-5678 goodbye 4ever
'would fail because the result would be
'1234-56784
'
Const charList = "-0123456789"
Dim workingString As String
Dim resultString As String
Dim LC As Integer

workingString = whatCell
If Len(workingString) > 8 Then
'has to be at least 9 long to contain a 1234-4321 entry!
For LC = 1 To Len(workingString)
If InStr(charList, Mid(workingString, LC, 1)) Then
resultString = resultString & Mid(workingString, LC, 1)
End If
Next
End If
If Len(resultString) = 9 And Mid(resultString, 5, 1) = "-" Then
GetNumGroup = resultString
Else
GetNumGroup = ""
End If
End Function



"MeatLightning" wrote:

> Hey all -
> I'm trying to extract a number from a text string. The text string
> varies in length and contents. The only thing that uniquely identifies the
> data I need to extract is its format - specifically: The number is always 4
> digits separated by a dash followed by 4 more digits. For example: 1234-1234.
>
> The trick is that there are other numbers in there that come close to the
> same format (ex: 12-1234).
>
> Any suggestions?
>
> Thanks in advance!
> -meat

Post some samples to give us an idea of what the strings look like.


--
Biff
Microsoft Excel MVP


"MeatLightning" <(E-Mail Removed)> wrote in message
news:27EE0B3A-FCA6-4864-8622-(E-Mail Removed)...
> Hey all -
> I'm trying to extract a number from a text string. The text string
> varies in length and contents. The only thing that uniquely identifies the
> data I need to extract is its format - specifically: The number is always
> 4
> digits separated by a dash followed by 4 more digits. For example:
> 1234-1234.
>
> The trick is that there are other numbers in there that come close to the
> same format (ex: 12-1234).
>
> Any suggestions?
>
> Thanks in advance!
> -meat

Hmmm... first, the text string can be any length... your formula seems to
depend on it being 9 characters total?... second, I'd need both sets of 4
from either side of the "-".

ex: "texty mr textington in text town getting texted for no texting reason
other than to pass the text. sometimes 1234-1234 is texterrific. text."

I need a formula that returns "1234-1234"

"David Biddulph" wrote:

> Which number are you trying to extract? The first 4 digits, the last 4, or
> both?
>
> =IF(AND(LEN(A1)=9,MID(A1,5,1)="-"),LEFT(A1,4),"") would give the first 4.
> --
> David Biddulph
>
> "MeatLightning" <(E-Mail Removed)> wrote in message
> news:27EE0B3A-FCA6-4864-8622-(E-Mail Removed)...
> > Hey all -
> > I'm trying to extract a number from a text string. The text string
> > varies in length and contents. The only thing that uniquely identifies the
> > data I need to extract is its format - specifically: The number is always
> > 4
> > digits separated by a dash followed by 4 more digits. For example:
> > 1234-1234.
> >
> > The trick is that there are other numbers in there that come close to the
> > same format (ex: 12-1234).
> >
> > Any suggestions?
> >
> > Thanks in advance!
> > -meat
>
>
>

Thanks! If it's not possible to tackle this with a formula, I'll give your
suggestions a whirl.

"JLatham" wrote:

> A user defined function (UDF) like the one below might work for you - it's
> not complete or foolproof, but is a good basis for one.
>
> To use it, first put it into your workbook:
> Open the workbook. Press [Alt]+[F11] to enter the VB Editor and in the VBE,
> choose Insert | Module and copy and paste the code below into the empty code
> module presented to you. Close the VB Editor.
>
> To use it in a worksheet, enter a formula such as
> =GetNumGroup(A5)
> where A5 is the cell containing the text you want to find the number group
> in. So, if
> A5 has "hello 1234-9876" in it, whatever cell you enter the =GetNumGroup(A5)
> should display "1234-9876".
> If no 4-4 group is found, the cell with the formula will remain blank.
>
> Hope this helps a bit.
>
> The code:
>
> Function GetNumGroup(whatCell) As String
> 'only works properly if there's just one
> 'group of possible digits.
> 'Examples:
> ' hello 1234-5678 goodbye
> 'would be ok and found, but
> ' hello 1234-5678 goodbye 4ever
> 'would fail because the result would be
> '1234-56784
> '
> Const charList = "-0123456789"
> Dim workingString As String
> Dim resultString As String
> Dim LC As Integer
>
> workingString = whatCell
> If Len(workingString) > 8 Then
> 'has to be at least 9 long to contain a 1234-4321 entry!
> For LC = 1 To Len(workingString)
> If InStr(charList, Mid(workingString, LC, 1)) Then
> resultString = resultString & Mid(workingString, LC, 1)
> End If
> Next
> End If
> If Len(resultString) = 9 And Mid(resultString, 5, 1) = "-" Then
> GetNumGroup = resultString
> Else
> GetNumGroup = ""
> End If
> End Function
>
>
>
> "MeatLightning" wrote:
>
> > Hey all -
> > I'm trying to extract a number from a text string. The text string
> > varies in length and contents. The only thing that uniquely identifies the
> > data I need to extract is its format - specifically: The number is always 4
> > digits separated by a dash followed by 4 more digits. For example: 1234-1234.
> >
> > The trick is that there are other numbers in there that come close to the
> > same format (ex: 12-1234).
> >
> > Any suggestions?
> >
> > Thanks in advance!
> > -meat

Or... even more better:

ex: "texty mr textington in text town getting texted for no texting reason
other than to pass the text. sometimes 1234-1234 is texterrific. but 12-1234
is pretty much never textastical. text."

I need a formula that returns "1234-1234

"MeatLightning" wrote:

> Hmmm... first, the text string can be any length... your formula seems to
> depend on it being 9 characters total?... second, I'd need both sets of 4
> from either side of the "-".
>
> ex: "texty mr textington in text town getting texted for no texting reason
> other than to pass the text. sometimes 1234-1234 is texterrific. text."
>
> I need a formula that returns "1234-1234"
>
> "David Biddulph" wrote:
>
> > Which number are you trying to extract? The first 4 digits, the last 4, or
> > both?
> >
> > =IF(AND(LEN(A1)=9,MID(A1,5,1)="-"),LEFT(A1,4),"") would give the first 4.
> > --
> > David Biddulph
> >
> > "MeatLightning" <(E-Mail Removed)> wrote in message
> > news:27EE0B3A-FCA6-4864-8622-(E-Mail Removed)...
> > > Hey all -
> > > I'm trying to extract a number from a text string. The text string
> > > varies in length and contents. The only thing that uniquely identifies the
> > > data I need to extract is its format - specifically: The number is always
> > > 4
> > > digits separated by a dash followed by 4 more digits. For example:
> > > 1234-1234.
> > >
> > > The trick is that there are other numbers in there that come close to the
> > > same format (ex: 12-1234).
> > >
> > > Any suggestions?
> > >
> > > Thanks in advance!
> > > -meat
> >
> >
> >

Is there *always* a space before and after the number string?

This works on the sample you posted:

=MID(A1,SEARCH(" ????-???? ",A1)+1,9)

--
Biff
Microsoft Excel MVP


"MeatLightning" <(E-Mail Removed)> wrote in message
news:B4157606-FA86-45A8-8C2C-(E-Mail Removed)...
> Or... even more better:
>
> ex: "texty mr textington in text town getting texted for no texting reason
> other than to pass the text. sometimes 1234-1234 is texterrific. but
> 12-1234
> is pretty much never textastical. text."
>
> I need a formula that returns "1234-1234
>
> "MeatLightning" wrote:
>
>> Hmmm... first, the text string can be any length... your formula seems to
>> depend on it being 9 characters total?... second, I'd need both sets of 4
>> from either side of the "-".
>>
>> ex: "texty mr textington in text town getting texted for no texting
>> reason
>> other than to pass the text. sometimes 1234-1234 is texterrific. text."
>>
>> I need a formula that returns "1234-1234"
>>
>> "David Biddulph" wrote:
>>
>> > Which number are you trying to extract? The first 4 digits, the last
>> > 4, or
>> > both?
>> >
>> > =IF(AND(LEN(A1)=9,MID(A1,5,1)="-"),LEFT(A1,4),"") would give the first
>> > 4.
>> > --
>> > David Biddulph
>> >
>> > "MeatLightning" <(E-Mail Removed)> wrote in
>> > message
>> > news:27EE0B3A-FCA6-4864-8622-(E-Mail Removed)...
>> > > Hey all -
>> > > I'm trying to extract a number from a text string. The text
>> > > string
>> > > varies in length and contents. The only thing that uniquely
>> > > identifies the
>> > > data I need to extract is its format - specifically: The number is
>> > > always
>> > > 4
>> > > digits separated by a dash followed by 4 more digits. For example:
>> > > 1234-1234.
>> > >
>> > > The trick is that there are other numbers in there that come close to
>> > > the
>> > > same format (ex: 12-1234).
>> > >
>> > > Any suggestions?
>> > >
>> > > Thanks in advance!
>> > > -meat
>> >
>> >
>> >

Ah! That's really close... No, there is not always a space before and after
the number.

"T. Valko" wrote:

> Is there *always* a space before and after the number string?
>
> This works on the sample you posted:
>
> =MID(A1,SEARCH(" ????-???? ",A1)+1,9)
>
> --
> Biff
> Microsoft Excel MVP
>
>
> "MeatLightning" <(E-Mail Removed)> wrote in message
> news:B4157606-FA86-45A8-8C2C-(E-Mail Removed)...
> > Or... even more better:
> >
> > ex: "texty mr textington in text town getting texted for no texting reason
> > other than to pass the text. sometimes 1234-1234 is texterrific. but
> > 12-1234
> > is pretty much never textastical. text."
> >
> > I need a formula that returns "1234-1234
> >
> > "MeatLightning" wrote:
> >
> >> Hmmm... first, the text string can be any length... your formula seems to
> >> depend on it being 9 characters total?... second, I'd need both sets of 4
> >> from either side of the "-".
> >>
> >> ex: "texty mr textington in text town getting texted for no texting
> >> reason
> >> other than to pass the text. sometimes 1234-1234 is texterrific. text."
> >>
> >> I need a formula that returns "1234-1234"
> >>
> >> "David Biddulph" wrote:
> >>
> >> > Which number are you trying to extract? The first 4 digits, the last
> >> > 4, or
> >> > both?
> >> >
> >> > =IF(AND(LEN(A1)=9,MID(A1,5,1)="-"),LEFT(A1,4),"") would give the first
> >> > 4.
> >> > --
> >> > David Biddulph
> >> >
> >> > "MeatLightning" <(E-Mail Removed)> wrote in
> >> > message
> >> > news:27EE0B3A-FCA6-4864-8622-(E-Mail Removed)...
> >> > > Hey all -
> >> > > I'm trying to extract a number from a text string. The text
> >> > > string
> >> > > varies in length and contents. The only thing that uniquely
> >> > > identifies the
> >> > > data I need to extract is its format - specifically: The number is
> >> > > always
> >> > > 4
> >> > > digits separated by a dash followed by 4 more digits. For example:
> >> > > 1234-1234.
> >> > >
> >> > > The trick is that there are other numbers in there that come close to
> >> > > the
> >> > > same format (ex: 12-1234).
> >> > >
> >> > > Any suggestions?
> >> > >
> >> > > Thanks in advance!
> >> > > -meat
> >> >
> >> >
> >> >
>
>
>

You'll need to post several representative samples so we can see what we're
dealing with.

--
Biff
Microsoft Excel MVP


"MeatLightning" <(E-Mail Removed)> wrote in message
news:0E975180-EEF2-4A29-83E4-(E-Mail Removed)...
> Ah! That's really close... No, there is not always a space before and
> after
> the number.
>
> "T. Valko" wrote:
>
>> Is there *always* a space before and after the number string?
>>
>> This works on the sample you posted:
>>
>> =MID(A1,SEARCH(" ????-???? ",A1)+1,9)
>>
>> --
>> Biff
>> Microsoft Excel MVP
>>
>>
>> "MeatLightning" <(E-Mail Removed)> wrote in
>> message
>> news:B4157606-FA86-45A8-8C2C-(E-Mail Removed)...
>> > Or... even more better:
>> >
>> > ex: "texty mr textington in text town getting texted for no texting
>> > reason
>> > other than to pass the text. sometimes 1234-1234 is texterrific. but
>> > 12-1234
>> > is pretty much never textastical. text."
>> >
>> > I need a formula that returns "1234-1234
>> >
>> > "MeatLightning" wrote:
>> >
>> >> Hmmm... first, the text string can be any length... your formula seems
>> >> to
>> >> depend on it being 9 characters total?... second, I'd need both sets
>> >> of 4
>> >> from either side of the "-".
>> >>
>> >> ex: "texty mr textington in text town getting texted for no texting
>> >> reason
>> >> other than to pass the text. sometimes 1234-1234 is texterrific.
>> >> text."
>> >>
>> >> I need a formula that returns "1234-1234"
>> >>
>> >> "David Biddulph" wrote:
>> >>
>> >> > Which number are you trying to extract? The first 4 digits, the
>> >> > last
>> >> > 4, or
>> >> > both?
>> >> >
>> >> > =IF(AND(LEN(A1)=9,MID(A1,5,1)="-"),LEFT(A1,4),"") would give the
>> >> > first
>> >> > 4.
>> >> > --
>> >> > David Biddulph
>> >> >
>> >> > "MeatLightning" <(E-Mail Removed)> wrote in
>> >> > message
>> >> > news:27EE0B3A-FCA6-4864-8622-(E-Mail Removed)...
>> >> > > Hey all -
>> >> > > I'm trying to extract a number from a text string. The text
>> >> > > string
>> >> > > varies in length and contents. The only thing that uniquely
>> >> > > identifies the
>> >> > > data I need to extract is its format - specifically: The number is
>> >> > > always
>> >> > > 4
>> >> > > digits separated by a dash followed by 4 more digits. For example:
>> >> > > 1234-1234.
>> >> > >
>> >> > > The trick is that there are other numbers in there that come close
>> >> > > to
>> >> > > the
>> >> > > same format (ex: 12-1234).
>> >> > >
>> >> > > Any suggestions?
>> >> > >
>> >> > > Thanks in advance!
>> >> > > -meat
>> >> >
>> >> >
>> >> >
>>
>>
>>

OK here goes:

#1234-1234.
1234-1234
1234-1234
text1234-1234
text1234-1234text

Does that help?

If there was a way to search for a number in that format, that'd nail it.
For example, instead of "????-????", you'd use "####-####"


"T. Valko" wrote:

> You'll need to post several representative samples so we can see what we're
> dealing with.
>
> --
> Biff
> Microsoft Excel MVP
>
>
> "MeatLightning" <(E-Mail Removed)> wrote in message
> news:0E975180-EEF2-4A29-83E4-(E-Mail Removed)...
> > Ah! That's really close... No, there is not always a space before and
> > after
> > the number.
> >
> > "T. Valko" wrote:
> >
> >> Is there *always* a space before and after the number string?
> >>
> >> This works on the sample you posted:
> >>
> >> =MID(A1,SEARCH(" ????-???? ",A1)+1,9)
> >>
> >> --
> >> Biff
> >> Microsoft Excel MVP
> >>
> >>
> >> "MeatLightning" <(E-Mail Removed)> wrote in
> >> message
> >> news:B4157606-FA86-45A8-8C2C-(E-Mail Removed)...
> >> > Or... even more better:
> >> >
> >> > ex: "texty mr textington in text town getting texted for no texting
> >> > reason
> >> > other than to pass the text. sometimes 1234-1234 is texterrific. but
> >> > 12-1234
> >> > is pretty much never textastical. text."
> >> >
> >> > I need a formula that returns "1234-1234
> >> >
> >> > "MeatLightning" wrote:
> >> >
> >> >> Hmmm... first, the text string can be any length... your formula seems
> >> >> to
> >> >> depend on it being 9 characters total?... second, I'd need both sets
> >> >> of 4
> >> >> from either side of the "-".
> >> >>
> >> >> ex: "texty mr textington in text town getting texted for no texting
> >> >> reason
> >> >> other than to pass the text. sometimes 1234-1234 is texterrific.
> >> >> text."
> >> >>
> >> >> I need a formula that returns "1234-1234"
> >> >>
> >> >> "David Biddulph" wrote:
> >> >>
> >> >> > Which number are you trying to extract? The first 4 digits, the
> >> >> > last
> >> >> > 4, or
> >> >> > both?
> >> >> >
> >> >> > =IF(AND(LEN(A1)=9,MID(A1,5,1)="-"),LEFT(A1,4),"") would give the
> >> >> > first
> >> >> > 4.
> >> >> > --
> >> >> > David Biddulph
> >> >> >
> >> >> > "MeatLightning" <(E-Mail Removed)> wrote in
> >> >> > message
> >> >> > news:27EE0B3A-FCA6-4864-8622-(E-Mail Removed)...
> >> >> > > Hey all -
> >> >> > > I'm trying to extract a number from a text string. The text
> >> >> > > string
> >> >> > > varies in length and contents. The only thing that uniquely
> >> >> > > identifies the
> >> >> > > data I need to extract is its format - specifically: The number is
> >> >> > > always
> >> >> > > 4
> >> >> > > digits separated by a dash followed by 4 more digits. For example:
> >> >> > > 1234-1234.
> >> >> > >
> >> >> > > The trick is that there are other numbers in there that come close
> >> >> > > to
> >> >> > > the
> >> >> > > same format (ex: 12-1234).
> >> >> > >
> >> >> > > Any suggestions?
> >> >> > >
> >> >> > > Thanks in advance!
> >> >> > > -meat
> >> >> >
> >> >> >
> >> >> >
> >>
> >>
> >>
>
>
>