Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help with MID Function?
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? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help with MID Function?
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? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help with MID Function?
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? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help with MID Function?
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? |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help with MID Function?
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, |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help with MID Function?
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, |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help with MID Function?
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? |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help with MID Function?
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 |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help with MID Function?
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? |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help with MID Function?
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? |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help with MID Function?
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! |
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help with MID Function?
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? |
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help with MID Function?
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? |
#14
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help with MID Function?
On Tue, 3 Feb 2009 19:28:30 -0800 (PST), KLZA wrote:
Glad to help. Thanks for the feedback. --ron |
#15
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help with MID Function?
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 |
#16
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help with MID Function?
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 |
#17
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help with MID Function?
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? |
#18
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help with MID Function?
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 |
#19
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help with MID Function?
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? |
#20
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help with MID Function?
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 is<g)? -- 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? |
#21
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#22
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help with MID Function?
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 |
#23
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help with MID Function?
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? |
#24
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help with MID Function?
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 |
#25
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help with MID Function?
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 is<g)? -- 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? |
#26
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help with MID Function?
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 |
#27
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help with MID Function?
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.. |
#28
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help with MID Function?
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 |
#29
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help with MID Function?
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
ISBLANK function not working when cell is blank dut to function re | Excel Discussion (Misc queries) | |||
copy of excel file not showing formulal/function in the function b | Excel Discussion (Misc queries) | |||
LINKEDRANGE function - a complement to the PULL function (for getting values from a closed workbook) | Excel Worksheet Functions | |||
Offset function with nested match function not finding host ss. | Excel Worksheet Functions | |||
Emulate Index/Match combo function w/ VBA custom function | Excel Worksheet Functions |