Hi. I may not have explained myself well enough in my last post. So
here goes again.
I'm trying to capture / extract specific data within a string of text
in a cell. I need to capture a string of numeric characters plus only
the first alpha character immediately after the numbers. The numbers
of alpha characters varies before the numeric characters. So my cells
could look like this: TTTTT10TTTTTT or TTT1000TTTT or
TTTTTTT1TTTTTTTTTTTT etc.. My output from the cells would be 10T or
1000T or 1T etc... The text string before and after the number string
vary. Can this be done?

Sure... just use the last formula I posted back in your first thread on this
problem; namely,

=MID(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"0123456 789")),LEN(LOOKUP(9E+307,--LEFT(MID(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"012 3456789")),999),ROW(1:999))))+1)

Change all the A1 references (4 of them) to whatever cell you have your text
in. This formula can be copied down if necessary.

--
Rick (MVP - Excel)


"KLZA" wrote in message
...
Hi. I may not have explained myself well enough in my last post. So
here goes again.
I'm trying to capture / extract specific data within a string of text
in a cell. I need to capture a string of numeric characters plus only
the first alpha character immediately after the numbers. The numbers
of alpha characters varies before the numeric characters. So my cells
could look like this: TTTTT10TTTTTT or TTT1000TTTT or
TTTTTTT1TTTTTTTTTTTT etc.. My output from the cells would be 10T or
1000T or 1T etc... The text string before and after the number string
vary. Can this be done?

On Feb 3, 7:35*pm, "Rick Rothstein"
wrote:
Sure... just use the last formula I posted back in your first thread on this
problem; namely,

=MID(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"0123456 789")),LEN(LOOKUP(9E+307,*--LEFT(MID(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"012 3456789")),999),ROW(1:9*99))))+1)

Change all the A1 references (4 of them) to whatever cell you have your text
in. This formula can be copied down if necessary.

--
Rick (MVP - Excel)

"KLZA" wrote in message

...



Hi. *I may not have explained myself well enough in my last post. *So
here goes again.
I'm trying to capture / extract specific data within a string of text
in a cell. *I need to capture a string of numeric characters plus only
the first alpha character immediately after the numbers. *The numbers
of alpha characters varies before the numeric characters. *So my cells
could look like this: *TTTTT10TTTTTT or TTT1000TTTT or
TTTTTTT1TTTTTTTTTTTT etc.. * My output from the cells would be 10T or
1000T or 1T etc... *The text string before and after the number string
vary. *Can this be done?- Hide quoted text -

- Show quoted text -

Hi. First, thanks for the help. The formula doesn't seem to work.
Can someone test it or explain what I'm possibly doing wrong?

Can you describe what "doesn't seem to work" means? What do you see the
formula returning? Did you change the cell references (the A1's) to the cell
that has your text?

Try this experiment. Put one of your text strings in A1; then click in A3 to
activate it (really, any cell will do); and then copy/paste the formula I
posted (do not try to re-type it) into the Formula Bar. and hit Enter. What
is displayed in A3?

--
Rick (MVP - Excel)


"KLZA" wrote in message
...
On Feb 3, 7:35 pm, "Rick Rothstein"
wrote:
Sure... just use the last formula I posted back in your first thread on
this
problem; namely,

=MID(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"0123456 789")),LEN(LOOKUP(9E+307,*--LEFT(MID(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"012 3456789")),999),ROW(1:9*99))))+1)

Change all the A1 references (4 of them) to whatever cell you have your
text
in. This formula can be copied down if necessary.

--
Rick (MVP - Excel)

"KLZA" wrote in message

...



Hi. I may not have explained myself well enough in my last post. So
here goes again.
I'm trying to capture / extract specific data within a string of text
in a cell. I need to capture a string of numeric characters plus only
the first alpha character immediately after the numbers. The numbers
of alpha characters varies before the numeric characters. So my cells
could look like this: TTTTT10TTTTTT or TTT1000TTTT or
TTTTTTT1TTTTTTTTTTTT etc.. My output from the cells would be 10T or
1000T or 1T etc... The text string before and after the number string
vary. Can this be done?- Hide quoted text -

- Show quoted text -

Hi. First, thanks for the help. The formula doesn't seem to work.
Can someone test it or explain what I'm possibly doing wrong?

On Feb 3, 9:13*pm, "Rick Rothstein"
wrote:
Can you describe what "doesn't seem to work" means? What do you see the
formula returning? Did you change the cell references (the A1's) to the cell
that has your text?

Try this experiment. Put one of your text strings in A1; then click in A3 to
activate it (really, any cell will do); and then copy/paste the formula I
posted (do not try to re-type it) into the Formula Bar. and hit Enter. What
is displayed in A3?

--
Rick (MVP - Excel)

"KLZA" wrote in message

...
On Feb 3, 7:35 pm, "Rick Rothstein"





wrote:
Sure... just use the last formula I posted back in your first thread on
this
problem; namely,

=MID(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"0123456 789")),LEN(LOOKUP(9E+307,**--LEFT(MID(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"012 3456789")),999),ROW(1:*9*99))))+1)

Change all the A1 references (4 of them) to whatever cell you have your
text
in. This formula can be copied down if necessary.

--
Rick (MVP - Excel)

"KLZA" wrote in message

....

Hi. I may not have explained myself well enough in my last post. So
here goes again.
I'm trying to capture / extract specific data within a string of text
in a cell. I need to capture a string of numeric characters plus only
the first alpha character immediately after the numbers. The numbers
of alpha characters varies before the numeric characters. So my cells
could look like this: TTTTT10TTTTTT or TTT1000TTTT or
TTTTTTT1TTTTTTTTTTTT etc.. My output from the cells would be 10T or
1000T or 1T etc... The text string before and after the number string
vary. Can this be done?- Hide quoted text -

- Show quoted text -

Hi. *First, thanks for the help. *The formula doesn't seem to work.
Can someone test it or explain what I'm possibly doing wrong?- Hide quoted text -

- Show quoted text -

I get an error after 9E+307,

I'm not sure what to tell you... I tested the formula before I posted it and
I just tested it again and it works on my XL2003 worksheet without any
problems. You did copy/paste the formula into your Formula Bar, didn't you?

--
Rick (MVP - Excel)


"KLZA" wrote in message
...
On Feb 3, 9:13 pm, "Rick Rothstein"
wrote:
Can you describe what "doesn't seem to work" means? What do you see the
formula returning? Did you change the cell references (the A1's) to the
cell
that has your text?

Try this experiment. Put one of your text strings in A1; then click in A3
to
activate it (really, any cell will do); and then copy/paste the formula I
posted (do not try to re-type it) into the Formula Bar. and hit Enter.
What
is displayed in A3?

--
Rick (MVP - Excel)

"KLZA" wrote in message

...
On Feb 3, 7:35 pm, "Rick Rothstein"





wrote:
Sure... just use the last formula I posted back in your first thread on
this
problem; namely,

=MID(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"0123456 789")),LEN(LOOKUP(9E+307,**--LEFT(MID(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"012 3456789")),999),ROW(1:*9*99))))+1)

Change all the A1 references (4 of them) to whatever cell you have your
text
in. This formula can be copied down if necessary.

--
Rick (MVP - Excel)

"KLZA" wrote in message

...

Hi. I may not have explained myself well enough in my last post. So
here goes again.
I'm trying to capture / extract specific data within a string of text
in a cell. I need to capture a string of numeric characters plus only
the first alpha character immediately after the numbers. The numbers
of alpha characters varies before the numeric characters. So my cells
could look like this: TTTTT10TTTTTT or TTT1000TTTT or
TTTTTTT1TTTTTTTTTTTT etc.. My output from the cells would be 10T or
1000T or 1T etc... The text string before and after the number string
vary. Can this be done?- Hide quoted text -

- Show quoted text -

Hi. First, thanks for the help. The formula doesn't seem to work.
Can someone test it or explain what I'm possibly doing wrong?- Hide quoted
text -

- Show quoted text -

I get an error after 9E+307,

On Tue, 3 Feb 2009 16:17:46 -0800 (PST), KLZA wrote:

Hi. I may not have explained myself well enough in my last post. So
here goes again.
I'm trying to capture / extract specific data within a string of text
in a cell. I need to capture a string of numeric characters plus only
the first alpha character immediately after the numbers. The numbers
of alpha characters varies before the numeric characters. So my cells
could look like this: TTTTT10TTTTTT or TTT1000TTTT or
TTTTTTT1TTTTTTTTTTTT etc.. My output from the cells would be 10T or
1000T or 1T etc... The text string before and after the number string
vary. Can this be done?



Repeating my last post:

Easy to do with a UDF:

To enter this <alt-F11 opens the VB Editor. Ensure your project is
highlighted in the Project Explorer window, then Insert/Module and paste the
code below into the window that opens.

To use this, enter a formula of the type:

=extrNumsPlusOne(A1)

The pattern (in the UDF below) will find the first series of digits and a
following single alpha character.

==============================
Function extrNumsPlusOne(str As String) As String
Dim re As Object, mc As Object
Set re = CreateObject("vbscript.regexp")
re.Pattern = "\d+[A-Za-z]"
If re.test(str) = True Then
Set mc = re.Execute(str)
extrNumsPlusOne = mc(0).Value
End If
End Function
============================
--ron
--ron

On Feb 3, 10:08*pm, Ron Rosenfeld wrote:
On Tue, 3 Feb 2009 16:17:46 -0800 (PST), KLZA wrote:
Hi. *I may not have explained myself well enough in my last post. *So
here goes again.
I'm trying to capture / extract specific data within a string of text
in a cell. *I need to capture a string of numeric characters plus only
the first alpha character immediately after the numbers. *The numbers
of alpha characters varies before the numeric characters. *So my cells
could look like this: *TTTTT10TTTTTT or TTT1000TTTT or
TTTTTTT1TTTTTTTTTTTT etc.. * My output from the cells would be 10T or
1000T or 1T etc... *The text string before and after the number string
vary. *Can this be done?

Repeating my last post:

Easy to do with a UDF:

To enter this <alt-F11 opens the VB Editor. *Ensure your project is
highlighted in the Project Explorer window, then Insert/Module and paste the
code below into the window that opens.

To use this, enter a formula of the type:

=extrNumsPlusOne(A1)

The pattern (in the UDF below) will find the first series of digits and a
following single alpha character.

==============================
Function extrNumsPlusOne(str As String) As String
Dim re As Object, mc As Object
Set re = CreateObject("vbscript.regexp")
re.Pattern = "\d+[A-Za-z]"
If re.test(str) = True Then
* * Set mc = re.Execute(str)
* * extrNumsPlusOne = mc(0).Value
End If
End Function
============================
--ron
--ron

Fantastic! You da man! This works great! 12800 line items - done!

On Feb 3, 10:28*pm, KLZA wrote:
On Feb 3, 10:08*pm, Ron Rosenfeld wrote:





On Tue, 3 Feb 2009 16:17:46 -0800 (PST), KLZA wrote:
Hi. *I may not have explained myself well enough in my last post. *So
here goes again.
I'm trying to capture / extract specific data within a string of text
in a cell. *I need to capture a string of numeric characters plus only
the first alpha character immediately after the numbers. *The numbers
of alpha characters varies before the numeric characters. *So my cells
could look like this: *TTTTT10TTTTTT or TTT1000TTTT or
TTTTTTT1TTTTTTTTTTTT etc.. * My output from the cells would be 10T or
1000T or 1T etc... *The text string before and after the number string
vary. *Can this be done?

Repeating my last post:

Easy to do with a UDF:

To enter this <alt-F11 opens the VB Editor. *Ensure your project is
highlighted in the Project Explorer window, then Insert/Module and paste the
code below into the window that opens.

To use this, enter a formula of the type:

=extrNumsPlusOne(A1)

The pattern (in the UDF below) will find the first series of digits and a
following single alpha character.

==============================
Function extrNumsPlusOne(str As String) As String
Dim re As Object, mc As Object
Set re = CreateObject("vbscript.regexp")
re.Pattern = "\d+[A-Za-z]"
If re.test(str) = True Then
* * Set mc = re.Execute(str)
* * extrNumsPlusOne = mc(0).Value
End If
End Function
============================
--ron
--ron

Fantastic! *You da man! *This works great! *12800 line items - done!- Hide quoted text -

- Show quoted text -

How do I modify to extract only the number?

On Tue, 3 Feb 2009 19:33:08 -0800 (PST), KLZA wrote:

=extrNumsPlusOne(A1)

The pattern (in the UDF below) will find the first series of digits and a
following single alpha character.

==============================
Function extrNumsPlusOne(str As String) As String
Dim re As Object, mc As Object
Set re = CreateObject("vbscript.regexp")
re.Pattern = "\d+[A-Za-z]"
If re.test(str) = True Then
* * Set mc = re.Execute(str)
* * extrNumsPlusOne = mc(0).Value
End If
End Function
============================
--ron
--ron

Fantastic! *You da man! *This works great! *12800 line items - done!- Hide quoted text -

- Show quoted text -

How do I modify to extract only the number?

In the UDF, just change this line:

re.Pattern = "\d+"

"\d+" tells the function to return the first series of digits in your string.

(The original "\d+[A-Za-z]" told it to return the first series of digits and
the following single letter).

--ron

Here is a modification to Ron's UDF which will let you specify whether to
return the letter after the number or not...

Function extrNumsPlusOne(str As String, Optional GetChar _
As Boolean = True) As String
Dim re As Object, mc As Object
Set re = CreateObject("vbscript.regexp")
re.Pattern = "\d+"
If GetChar Then re.Pattern = re.Pattern & "[A-Za-z]"
If re.test(str) = True Then
Set mc = re.Execute(str)
extrNumsPlusOne = mc(0).Value
End If
End Function

The default is to return the number and the letter after it; however, if you
specify the optional Boolean argument as False (or 0), only the number is
returned. So, to get only the number, do either of these...

=extrNumsPlusOne(A1,False)
=extrNumsPlusOne(A1,0)

To ge the trailing letter with the number, you can do either of these...

=extrNumsPlusOne(A1)
=extrNumsPlusOne(A1,True)
=extrNumsPlusOne(A1,1)

And, just in case you were interested in pursuing the UDF I posted, here it
is modified to work in the same way I the modification I did to Ron's UDF...

Function GetNumberPlusOne(S As String, Optional GetCharacter _
As Boolean = True) As String
Dim X As Long, Y As Long
For X = 1 To Len(S)
If IsNumeric(Mid(S, X, 1)) Then
For Y = X + 1 To Len(S)
If Not IsNumeric(Mid(S, Y, 1)) Then
GetNumberPlusOne = Mid(S, X, Y - X - GetCharacter)
Exit For
End If
Next
Exit Function
End If
Next
End Function

--
Rick (MVP - Excel)


"KLZA" wrote in message
...
On Feb 3, 10:28 pm, KLZA wrote:
On Feb 3, 10:08 pm, Ron Rosenfeld wrote:





On Tue, 3 Feb 2009 16:17:46 -0800 (PST), KLZA
wrote:
Hi. I may not have explained myself well enough in my last post. So
here goes again.
I'm trying to capture / extract specific data within a string of text
in a cell. I need to capture a string of numeric characters plus only
the first alpha character immediately after the numbers. The numbers
of alpha characters varies before the numeric characters. So my cells
could look like this: TTTTT10TTTTTT or TTT1000TTTT or
TTTTTTT1TTTTTTTTTTTT etc.. My output from the cells would be 10T or
1000T or 1T etc... The text string before and after the number string
vary. Can this be done?

Repeating my last post:

Easy to do with a UDF:

To enter this <alt-F11 opens the VB Editor. Ensure your project is
highlighted in the Project Explorer window, then Insert/Module and paste
the
code below into the window that opens.

To use this, enter a formula of the type:

=extrNumsPlusOne(A1)

The pattern (in the UDF below) will find the first series of digits and
a
following single alpha character.

==============================
Function extrNumsPlusOne(str As String) As String
Dim re As Object, mc As Object
Set re = CreateObject("vbscript.regexp")
re.Pattern = "\d+[A-Za-z]"
If re.test(str) = True Then
Set mc = re.Execute(str)
extrNumsPlusOne = mc(0).Value
End If
End Function
============================
--ron
--ron

Fantastic! You da man! This works great! 12800 line items - done!- Hide
quoted text -

- Show quoted text -

How do I modify to extract only the number?

On Tue, 3 Feb 2009 19:33:08 -0800 (PST), KLZA wrote:

How do I modify to extract only the number?

It occurs to me that you may want something a bit more general. One way to do
that is to have "pattern" as one of the arguments of the UDF.

That being the case, you could enter the code below but use the following
functions:

To return the digits plus one letter:

=ReMid(A1,"\d+[A-Za-z]")

To return only the digits:

=ReMid(A1,"\d+")

============================
Option Explicit
Function ReMid(str As String, sPattern As String) As String
Dim re As Object, mc As Object
Set re = CreateObject("vbscript.regexp")
re.Pattern = sPattern
If re.test(str) = True Then
Set mc = re.Execute(str)
ReMid = mc(0).Value
End If
End Function
========================
--ron

On Feb 3, 11:28*pm, Ron Rosenfeld wrote:
On Tue, 3 Feb 2009 19:33:08 -0800 (PST), KLZA wrote:
How do I modify to extract only the number?

It occurs to me that you may want something a bit more general. *One way to do
that is to have "pattern" as one of the arguments of the UDF.

That being the case, you could enter the code below but use the following
functions:

To return the digits plus one letter:

=ReMid(A1,"\d+[A-Za-z]")

To return only the digits:

=ReMid(A1,"\d+")

============================
Option Explicit
Function ReMid(str As String, sPattern As String) As String
Dim re As Object, mc As Object
Set re = CreateObject("vbscript.regexp")
re.Pattern = sPattern
If re.test(str) = True Then
* * Set mc = re.Execute(str)
* * ReMid = mc(0).Value
End If
End Function
========================
--ron

Rick, Ron. Thanks for all of your help. I used the modules and they
work great. I love the support on these groups. Very grreatful.
Thanks again..

On Tue, 3 Feb 2009 19:28:30 -0800 (PST), KLZA wrote:



Glad to help. Thanks for the feedback.
--ron

Here is a modification to Ron's UDF which will let you specify whether to
return the letter after the number or not...

Function extrNumsPlusOne(str As String, Optional GetChar _
As Boolean = True) As String
Dim re As Object, mc As Object
Set re = CreateObject("vbscript.regexp")
re.Pattern = "\d+"
If GetChar Then re.Pattern = re.Pattern & "[A-Za-z]"
If re.test(str) = True Then
Set mc = re.Execute(str)
extrNumsPlusOne = mc(0).Value
End If
End Function

The default is to return the number and the letter after it; however, if you
specify the optional Boolean argument as False (or 0), only the number is
returned. So, to get only the number, do either of these...

=extrNumsPlusOne(A1,False)
=extrNumsPlusOne(A1,0)

To ge the trailing letter with the number, you can do either of these...

=extrNumsPlusOne(A1)
=extrNumsPlusOne(A1,True)
=extrNumsPlusOne(A1,1)

--
Rick (MVP - Excel)


"Ron Rosenfeld" wrote in message
...
On Tue, 3 Feb 2009 19:28:30 -0800 (PST), KLZA wrote:



Glad to help. Thanks for the feedback.
--ron

Sorry, Ron... I mis-posted this to your message instead of the OP's message.

--
Rick (MVP - Excel)


"Rick Rothstein" wrote in message
...
Here is a modification to Ron's UDF which will let you specify whether to
return the letter after the number or not...

Function extrNumsPlusOne(str As String, Optional GetChar _
As Boolean = True) As String
Dim re As Object, mc As Object
Set re = CreateObject("vbscript.regexp")
re.Pattern = "\d+"
If GetChar Then re.Pattern = re.Pattern & "[A-Za-z]"
If re.test(str) = True Then
Set mc = re.Execute(str)
extrNumsPlusOne = mc(0).Value
End If
End Function

The default is to return the number and the letter after it; however, if
you specify the optional Boolean argument as False (or 0), only the number
is returned. So, to get only the number, do either of these...

=extrNumsPlusOne(A1,False)
=extrNumsPlusOne(A1,0)

To ge the trailing letter with the number, you can do either of these...

=extrNumsPlusOne(A1)
=extrNumsPlusOne(A1,True)
=extrNumsPlusOne(A1,1)

--
Rick (MVP - Excel)


"Ron Rosenfeld" wrote in message
...
On Tue, 3 Feb 2009 19:28:30 -0800 (PST), KLZA wrote:



Glad to help. Thanks for the feedback.
--ron

Here's a VBA example, not as neat as a worksheet function but here ya go...

Cells A1:I1
TTTT100TT
cell J1 enter =GetNumAndChar(A1:I1)

Dragging the lower right corner of this cell to other locations create the
referrenced incremented ranges.

HTH

<begin copy omit this line
Option Explicit

Public Function GetNumAndChar(ByVal rRange As Excel.Range) As String

Dim in_value As String
Dim iRow, iCol, iposit, iStop As Integer
in_value = ""
iRow = 1

For iCol = 1 To rRange.Cells.Count
If rRange.Cells(iRow, iCol) = "." Then iCol = iCol + 1
If IsNumeric(rRange.Cells(iRow, iCol)) Then
in_value = in_value & rRange.Cells(iRow, iCol)
If IsNumeric(rRange.Cells(iRow, iCol)) And Not
IsNumeric(rRange.Cells(iRow, iCol + 1)) Then
in_value = in_value & rRange.Cells(iRow, iCol + 1)
Exit For
End If
End If
Next

If IsNull(in_value) Then
GetNumAndChar = " "
Else: GetNumAndChar = in_value
End If

End Function
<end copy omit this line

"KLZA" wrote:

Hi. I may not have explained myself well enough in my last post. So
here goes again.
I'm trying to capture / extract specific data within a string of text
in a cell. I need to capture a string of numeric characters plus only
the first alpha character immediately after the numbers. The numbers
of alpha characters varies before the numeric characters. So my cells
could look like this: TTTTT10TTTTTT or TTT1000TTTT or
TTTTTTT1TTTTTTTTTTTT etc.. My output from the cells would be 10T or
1000T or 1T etc... The text string before and after the number string
vary. Can this be done?

If you want to with a UDF as Ron has proposed, and if you are uncomfortable
with Regular Expressions (RegEx), here is a non-RegEx UDF for you to
consider...

Function GetNumberPlusOne(S As String) As String
Dim X As Long, Y As Long
For X = 1 To Len(S)
If IsNumeric(Mid(S, X, 1)) Then
For Y = X + 1 To Len(S)
If Not IsNumeric(Mid(S, Y, 1)) Then
GetNumberPlusOne = Mid(S, X, Y - X + 1)
Exit For
End If
Next
Exit Function
End If
Next
End Function

--
Rick (MVP - Excel)


"KLZA" wrote in message
...
Hi. I may not have explained myself well enough in my last post. So
here goes again.
I'm trying to capture / extract specific data within a string of text
in a cell. I need to capture a string of numeric characters plus only
the first alpha character immediately after the numbers. The numbers
of alpha characters varies before the numeric characters. So my cells
could look like this: TTTTT10TTTTTT or TTT1000TTTT or
TTTTTTT1TTTTTTTTTTTT etc.. My output from the cells would be 10T or
1000T or 1T etc... The text string before and after the number string
vary. Can this be done?