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

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

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

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

Here is one way

=MID(A2,SMALL(IF(ISNUMBER(MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1)*1),ROW(INDIRECT("1:"&LEN(A2)))),1),FIND(".",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...
> > >

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),FIND(".",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...
> > > >

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

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),FIND(".",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...
> > > >