Help with MID Function?

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 Tue, 3 Feb 2009 22:57:54 -0500, "Rick Rothstein"
wrote:

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

--
Rick (MVP - Excel)

No problem.

After first posting something, I realized he probably wanted an "either/or"
rather than just a change to numbers, so I just posted a more generalized
version which just makes "Pattern" one of the arguments in the UDF. That'd
make further modifications simpler, I think.
--ron

Here is a formula that works... provided, apparently, that you view it
somewhere other than Google Groups (see Biff's T. Valko's posting)...

=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+FIND(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))),A1)-MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"0123456789")))

--
Rick (MVP - Excel)


"Rick Rothstein" wrote in message
...
You are right... there is a problem with the formula IF your number starts
with one or more zeroes. I'll look into trying to correct it.

--
Rick (MVP - Excel)


"KLZA" wrote in message
...
On Feb 3, 9:44 pm, "Rick Rothstein"
wrote:
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,- Hide quoted text -

- Show quoted text -

Hey, I tested on two other machines and I think your formula may be
faulty. I'm using excel 2003 with different builds.. Can someone
else test this?

Well, simpler if you know RegEx patterns that is. g

--
Rick (MVP - Excel)


"Ron Rosenfeld" wrote in message
...
On Tue, 3 Feb 2009 22:57:54 -0500, "Rick Rothstein"
wrote:

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

--
Rick (MVP - Excel)

No problem.

After first posting something, I realized he probably wanted an
"either/or"
rather than just a change to numbers, so I just posted a more generalized
version which just makes "Pattern" one of the arguments in the UDF.
That'd
make further modifications simpler, I think.
--ron

I think those dashes are line break characters. You don't see them in the
post but they show up when you copy/paste.

--
Biff
Microsoft Excel MVP


"Rick Rothstein" wrote in message
...
Oh, that's cute! I wasn't aware of this problem with Google Groups. I'm
guessing there is no way to prevent that (other than to tell the poster
not to use Google Groups, that isg)?

--
Rick (MVP - Excel)


"T. Valko" wrote in message
...
While that may be an issue, the immediate problem is that the poster is
posting through Google Groups. Google Groups is notorious for inserting
formatting characters into formulas.

I went to Google Groups, found this thread, copied the formula into a
cell and got the error message.

Google had inserted 2 "-" (dashes) into the formula at these locations:

---LEFT
ROW(1:-999)


--
Biff
Microsoft Excel MVP


"Rick Rothstein" wrote in message
...
You are right... there is a problem with the formula IF your number
starts with one or more zeroes. I'll look into trying to correct it.

--
Rick (MVP - Excel)


"KLZA" wrote in message
...
On Feb 3, 9:44 pm, "Rick Rothstein"
wrote:
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,- Hide quoted text -

- Show quoted text -

Hey, I tested on two other machines and I think your formula may be
faulty. I'm using excel 2003 with different builds.. Can someone
else test this?

On Tue, 3 Feb 2009 23:47:13 -0500, "Rick Rothstein"
wrote:

Well, simpler if you know RegEx patterns that is. g

--
Rick (MVP - Excel)

That's true. There are similarities to the character lists used for the VBA
Like operator though. There are some shortcuts. For example \d is equivalent
to [0-9]. And there are modifiers so that patterns can be easily repeated.

And I do find something like "/d+[A-Za-z]" quicker to devise as a solution to
the OP's problem than:

=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&"0123456789")),999),ROW(1 :999))))
+1+FIND(LOOKUP(9E+307,--LEFT(MID(A1,MIN(FIND(
{0,1,2,3,4,5,6,7,8,9},A1&"0123456789")),999),ROW(1 :999))),A1)
-MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"0123456789")))

:-)
--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..

What? It took almost no time to devise that formula. Well, maybe I
exaggerated there just a **wee** bit.g

By the way, \d is also equivalent to # also (it is a shortcut for [0-9]) in
a Like operator pattern.

--
Rick (MVP - Excel)


"Ron Rosenfeld" wrote in message
...
On Tue, 3 Feb 2009 23:47:13 -0500, "Rick Rothstein"
wrote:

Well, simpler if you know RegEx patterns that is. g

--
Rick (MVP - Excel)

That's true. There are similarities to the character lists used for the
VBA
Like operator though. There are some shortcuts. For example \d is
equivalent
to [0-9]. And there are modifiers so that patterns can be easily
repeated.

And I do find something like "/d+[A-Za-z]" quicker to devise as a solution
to
the OP's problem than:

=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&"0123456789")),999),ROW(1 :999))))
+1+FIND(LOOKUP(9E+307,--LEFT(MID(A1,MIN(FIND(
{0,1,2,3,4,5,6,7,8,9},A1&"0123456789")),999),ROW(1 :999))),A1)
-MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"0123456789")))

:-)
--ron

On Wed, 4 Feb 2009 06:48:06 -0800 (PST), KLZA wrote:

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..

You're most welcome. Glad to help. Thanks for the feedback.
--ron