PC Review


Reply
Thread Tools Rate Thread

Extract number from text string based on number's format?

 
 
MeatLightning
Guest
Posts: n/a
 
      17th Nov 2008
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
 
Reply With Quote
 
 
 
 
JLatham
Guest
Posts: n/a
 
      17th Nov 2008
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

 
Reply With Quote
 
 
 
 
T. Valko
Guest
Posts: n/a
 
      17th Nov 2008
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:(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



 
Reply With Quote
 
MeatLightning
Guest
Posts: n/a
 
      18th Nov 2008
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:(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

>
>
>

 
Reply With Quote
 
MeatLightning
Guest
Posts: n/a
 
      18th Nov 2008
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

 
Reply With Quote
 
MeatLightning
Guest
Posts: n/a
 
      18th Nov 2008
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:(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

> >
> >
> >

 
Reply With Quote
 
T. Valko
Guest
Posts: n/a
 
      18th Nov 2008
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:(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:(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
>> >
>> >
>> >



 
Reply With Quote
 
MeatLightning
Guest
Posts: n/a
 
      18th Nov 2008
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:(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:(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
> >> >
> >> >
> >> >

>
>
>

 
Reply With Quote
 
T. Valko
Guest
Posts: n/a
 
      18th Nov 2008
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:(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:(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:(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
>> >> >
>> >> >
>> >> >

>>
>>
>>



 
Reply With Quote
 
MeatLightning
Guest
Posts: n/a
 
      18th Nov 2008
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:(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:(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:(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
> >> >> >
> >> >> >
> >> >> >
> >>
> >>
> >>

>
>
>

 
Reply With Quote
 
 
 
Reply

Thread Tools
Rate This Thread
Rate This Thread:

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
RE: Extract a number(s) from a text string Mike H Microsoft Excel Misc 0 21st Mar 2009 05:36 PM
Extract a number from a variable text string tipsy Microsoft Excel Misc 4 4th May 2008 03:28 AM
Extract a text string based on character kgiraffa Microsoft Excel Worksheet Functions 5 14th Mar 2008 01:54 AM
Extract number from text/number string.. =?Utf-8?B?bmFzdGVjaA==?= Microsoft Excel Misc 5 5th Jul 2006 11:21 PM
Extract a number from a text string? Brian Microsoft Excel Discussion 8 9th Jan 2006 10:17 PM


Features
 

Advertising
 

Newsgroups
 


All times are GMT +1. The time now is 09:17 PM.