|
Challenging Formula
Hi...
I have 4000 entries in column A similar to... C:\Common Standards Reports\Spare Parts 0606.xls C:\Common Standards Reports\Steering wheels 110606.xls C:\Common Standards Reports\yellow paint 22876.xls I need the corresponding/adjacent cell in column B to strip out the number string so that said cells read: 0606 110606 22876 Can this be done? Thanks in advance... |
Challenging Formula
You could strip out the file name fairly quickly with
=Right(A1,len(A1)-28) (I hope I counted correctly) "Gordon" wrote: Hi... I have 4000 entries in column A similar to... C:\Common Standards Reports\Spare Parts 0606.xls C:\Common Standards Reports\Steering wheels 110606.xls C:\Common Standards Reports\yellow paint 22876.xls I need the corresponding/adjacent cell in column B to strip out the number string so that said cells read: 0606 110606 22876 Can this be done? Thanks in advance... |
Challenging Formula
Hi
This didn't work. The text string varies in length pretty much every time as does the number string and the length of the number. Thanks anyway. Gordon. "Barb Reinhardt" wrote: You could strip out the file name fairly quickly with =Right(A1,len(A1)-28) (I hope I counted correctly) "Gordon" wrote: Hi... I have 4000 entries in column A similar to... C:\Common Standards Reports\Spare Parts 0606.xls C:\Common Standards Reports\Steering wheels 110606.xls C:\Common Standards Reports\yellow paint 22876.xls I need the corresponding/adjacent cell in column B to strip out the number string so that said cells read: 0606 110606 22876 Can this be done? Thanks in advance... |
Challenging Formula
There is no built in function to help you with this. You need to create a
User Defiend Function. In a standard code module (the type that you get when you record a macro) place this code... Public Function FirstNumber(ByVal InputString As String) As Integer Dim intCounter As Integer Dim intStringLength As Integer Dim intReturnValue As Integer intReturnValue = -1 intStringLength = Len(InputString) For intCounter = 1 To intStringLength If IsNumeric(Mid(InputString, intCounter, 1)) Then intReturnValue = intCounter Exit For End If Next intCounter FirstNumber = intReturnValue End Function then you can use a function like this (assuming your text is in cell A1) =MID(A1, firstnumber(A1), LEN(A1)-FIND(".", A1)+1) -- HTH... Jim Thomlinson "Gordon" wrote: Hi... I have 4000 entries in column A similar to... C:\Common Standards Reports\Spare Parts 0606.xls C:\Common Standards Reports\Steering wheels 110606.xls C:\Common Standards Reports\yellow paint 22876.xls I need the corresponding/adjacent cell in column B to strip out the number string so that said cells read: 0606 110606 22876 Can this be done? Thanks in advance... |
Challenging Formula
Here is one way
=MID(A2,SMALL(IF(ISNUMBER(MID(A2,ROW(INDIRECT("1:" &LEN(A2))),1)*1),ROW(INDIRECT("1:"&LEN(A2)))),1),F IND(".",A2)-SMALL(IF(ISNUMBER(MID(A2,ROW(INDIRECT("1:"&LEN(A2) )),1)*1),ROW(INDIRECT("1:"&LEN(A2)))),1)) Entered with Ctrl+shift+enter rather than just enter since this is an array formula. As written extracts the number from a string in A2. -- Regards, Tom Ogilvy "Gordon" wrote: Hi This didn't work. The text string varies in length pretty much every time as does the number string and the length of the number. Thanks anyway. Gordon. "Barb Reinhardt" wrote: You could strip out the file name fairly quickly with =Right(A1,len(A1)-28) (I hope I counted correctly) "Gordon" wrote: Hi... I have 4000 entries in column A similar to... C:\Common Standards Reports\Spare Parts 0606.xls C:\Common Standards Reports\Steering wheels 110606.xls C:\Common Standards Reports\yellow paint 22876.xls I need the corresponding/adjacent cell in column B to strip out the number string so that said cells read: 0606 110606 22876 Can this be done? Thanks in advance... |
Challenging Formula
Tom...
Thanks...that worked a treat. Gordon. "Tom Ogilvy" wrote: Here is one way =MID(A2,SMALL(IF(ISNUMBER(MID(A2,ROW(INDIRECT("1:" &LEN(A2))),1)*1),ROW(INDIRECT("1:"&LEN(A2)))),1),F IND(".",A2)-SMALL(IF(ISNUMBER(MID(A2,ROW(INDIRECT("1:"&LEN(A2) )),1)*1),ROW(INDIRECT("1:"&LEN(A2)))),1)) Entered with Ctrl+shift+enter rather than just enter since this is an array formula. As written extracts the number from a string in A2. -- Regards, Tom Ogilvy "Gordon" wrote: Hi This didn't work. The text string varies in length pretty much every time as does the number string and the length of the number. Thanks anyway. Gordon. "Barb Reinhardt" wrote: You could strip out the file name fairly quickly with =Right(A1,len(A1)-28) (I hope I counted correctly) "Gordon" wrote: Hi... I have 4000 entries in column A similar to... C:\Common Standards Reports\Spare Parts 0606.xls C:\Common Standards Reports\Steering wheels 110606.xls C:\Common Standards Reports\yellow paint 22876.xls I need the corresponding/adjacent cell in column B to strip out the number string so that said cells read: 0606 110606 22876 Can this be done? Thanks in advance... |
Challenging Formula
Hi Gordon,
You can try this VB code.... Sub GetNumbers() Dim Ipos As Integer Dim Iloop As Double Dim Iloop1 As Integer Dim RowCount As Double 'Turn off warnings, etc. Application.ScreenUpdating = False Application.DisplayAlerts = False RowCount = Range("A65536").End(xlUp).Row For Iloop = 1 To RowCount Ipos = InStr(1, Cells(Iloop, "A"), ".") For Iloop1 = 1 To Len(Cells(Iloop, "A")) If IsNumeric(Mid(Cells(Iloop, "A"), Iloop1, 1)) Then Cells(Iloop, "B") = Mid(Cells(Iloop, "A"), Iloop1, Ipos - Iloop1) Exit For End If Next Iloop1 Next Iloop 'Turn on warnings, etc. Application.ScreenUpdating = True Application.DisplayAlerts = True End Sub -- Ken Hudson "Gordon" wrote: Hi... I have 4000 entries in column A similar to... C:\Common Standards Reports\Spare Parts 0606.xls C:\Common Standards Reports\Steering wheels 110606.xls C:\Common Standards Reports\yellow paint 22876.xls I need the corresponding/adjacent cell in column B to strip out the number string so that said cells read: 0606 110606 22876 Can this be done? Thanks in advance... |
Challenging Formula
Wow... I never even thought of that... Tom, you are a better man than I. Just
a note for Gord though. That formula uses Indirect which is volatile and so everywhere that it is used (4,000 cells in this case) will re-calc with every calculation. Gordon, once you have extracted the numbers you may want to copy and paste special values over those formulas (all but one so that you still have the fromula handy for the next time you want it) otherwise you may find that your spreadsheet will be running very slowly. Once again Tom, I tip my hat to you... -- HTH... Jim Thomlinson "Tom Ogilvy" wrote: Here is one way =MID(A2,SMALL(IF(ISNUMBER(MID(A2,ROW(INDIRECT("1:" &LEN(A2))),1)*1),ROW(INDIRECT("1:"&LEN(A2)))),1),F IND(".",A2)-SMALL(IF(ISNUMBER(MID(A2,ROW(INDIRECT("1:"&LEN(A2) )),1)*1),ROW(INDIRECT("1:"&LEN(A2)))),1)) Entered with Ctrl+shift+enter rather than just enter since this is an array formula. As written extracts the number from a string in A2. -- Regards, Tom Ogilvy "Gordon" wrote: Hi This didn't work. The text string varies in length pretty much every time as does the number string and the length of the number. Thanks anyway. Gordon. "Barb Reinhardt" wrote: You could strip out the file name fairly quickly with =Right(A1,len(A1)-28) (I hope I counted correctly) "Gordon" wrote: Hi... I have 4000 entries in column A similar to... C:\Common Standards Reports\Spare Parts 0606.xls C:\Common Standards Reports\Steering wheels 110606.xls C:\Common Standards Reports\yellow paint 22876.xls I need the corresponding/adjacent cell in column B to strip out the number string so that said cells read: 0606 110606 22876 Can this be done? Thanks in advance... |
| All times are GMT +1. The time now is 02:03 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com