Re: Lookup Question

["Hoggins, Graham" <hoggig@RAYOVAC.CO.UK>]

Thanks,

That will indeed do what I need and hopefully pointed me in the direction to
a formula driven answer. I have no problem with UDF's, but I like to to try
and use formula where ever possible for the benefit of work collegues.
        Just thinking about the method that you have used in the UDF, I
could you the SEARCH function e.g SEARCH(X,A1,1) to locate the position of
the 1st text char. Only problem is I'm not sure what Function or formula to
replace X with that will locate just text)

Regards
Graham

> -----Original Message-----
> From: Lee Fuller [mailto:Lee.Fuller@HEWITTBACONWOODROW.COM]
> Sent: 21 November 2003 13:21
> To: EXCEL-G@PEACH.EASE.LSOFT.COM
> Subject: Re: Lookup Question
>
>
> I couldn't see any way of doing using a formula. I've written this UDF
> which will do what you need, pretty naff though.  You just
> need to copy
> and paste this to a standard module then in your worksheet put
>
> =FINDTEXT(A1)
>
> It looks through the string one letter at a time, it it finds a number
> then it will move onto the next letter until it finds a
> letter, this will
> then return the next 3 letters as your code.  I've had to put
> Case "0", "1", "2", "3", "4", "5", "6", "7", "8", "9" because
> VBA will only return the numbers as strings otherwise it would
> have been simpler to use Case 0 - 9.
>
> Hope this helps.
>
> Public Function FindText(Find_Text As Range) As String
>     Dim TextCheck As String
>
>     For x = 1 To Len(Find_Text.Value)
>
>         TextCheck = Mid(Find_Text.Value, x, 1)
>
>         Select Case TextCheck
>
>         Case "0", "1", "2", "3", "4", "5", "6", "7", "8", "9"
>           'ignore if equals any of above
>
>         Case Else
>             FindText = Mid(Find_Text.Value, x, 3)
>             Exit For
>         End Select
>
>     Next x
>
> End Function
>
>
>
>
>
>
> "Hoggins, Graham" <hoggig@RAYOVAC.CO.UK>
> Sent by: "MS Excel General Q & A List" <EXCEL-G@PEACH.EASE.LSOFT.COM>
> 21/11/2003 13:04
> Please respond to "MS Excel General Q & A List"
>
>
>         To:     EXCEL-G@PEACH.EASE.LSOFT.COM
>         cc:
>
>         Subject:        Re: Lookup Question
>
>
>
> Thanks Don,
>
> I was hoping that this would be possible to do via formula rather than
> VBA.
> it was my understanding that the FIND and SEARCH function can
> only find
> one
> string at a time and not one of many from an aray.
>
> Regards
> Graham
>
> > -----Original Message-----
> > From: Don Guillett [mailto:donaldb@281.COM]
> > Sent: 21 November 2003 12:37
> > To: EXCEL-G@PEACH.EASE.LSOFT.COM
> > Subject: Re: Lookup Question
> >
> >
> > Look in vbe help index for FIND
> >
> > Don Guillett
> > SalesAid Software
> > donaldb@281.com
> > ----- Original Message -----
> > From: "Hoggins, Graham" <hoggig@RAYOVAC.CO.UK>
> > To: <EXCEL-G@PEACH.EASE.LSOFT.COM>
> > Sent: Friday, November 21, 2003 6:18 AM
> > Subject: Lookup Question
> >
> >
> > > Afternoon/morning all,
> > >
> > > I've setup up a workbook to process a flatfile of a
> > shipping bill that we
> > > receive weekly (approx 500 lines per week)
> > > Currently I lookup up a reference code against a table of
> > country and
> > > account codes so I can split and analysis the bill. e.g
> > >
> > > Ref             Amount
> > > VGE10000        100
> > > VGE10001        100
> > > VFE10003        200
> > > VNL10003        250
> > > MAR10004        50
> > >
> > >
> > > Lookup Table
> > >
> > > Code    Department              Account
> > > VGE     Germany         10100
> > > VFE     France          10200
> > > VNL     Netherlands             10300
> > > MAR     Marketing               10400
> > > SAL     Sales                   10500
> > >
> > >
> > > The formula I currently use  is
> > "vlookup((left($A1,3)),Lookup,2,false)"
> > >
> > > This works great if distribution department adhere to the
> > convention of
> > > prefixing the reference code with the 3 digit lookup code.
> > Unfortunately
> > as
> > > this isn't a perfect world it isn't always done and I get multiple
> > > variations on the code... eg. _VGE10000, 10000VGE, 10000_VGE, and
> > 10000VGE_
> > > There are also a number codes that do not need a prefix
> > code at all and
> > > these are displayed as a plain number.
> > >         At the moment I am manually correcting these codes
> > and moving the
> > > prefix code to the front so the vookup works.
> > > Is it possible to search the reference code to see if it
> > includes one of
> > the
> > > prefix codes regardless of position. If the  lookup code is
> > found, then I
> > > can determine the starting position and use the MID(0,0,0)
> > funtion to
> > return
> > > the lookup parameter?
> > >
> > > Any help would be appreciated and I'd really like to this
> > with formula.
> > >
> > > Regards
> > > Graham
> > > ****** IMPORTANT NOTICE ****** Please note that we reserve
> > the right to
> > > monitor and read any Emails sent and received by the
> > Company under the
> > > Telecommunications (Lawful Business Practice) (Interception of
> > > Communications) Regulations 2000.
> > >
> > >
> > --------------------------------------------------------------
> > ------------
> > > The EXCEL-G list is hosted on a Windows NT(TM) machine
> > running L-Soft
> > > international's LISTSERV(R) software.  For
> subscription/signoff info
> > > and archives, see
> > http://peach.ease.lsoft.com/archives/excel-g.html .
> > >                              COPYRIGHT INFO:
> > >
> >
> http://peach.ease.lsoft.com/scripts/wa.exe?SHOWTPL=COPYRIGHT&L=EXCEL-G
> > >
> > >
> >
> > --------------------------------------------------------------
> > ------------
> > The EXCEL-G list is hosted on a Windows NT(TM) machine
> running L-Soft
> > international's LISTSERV(R) software.  For subscription/signoff info
> > and archives, see
> http://peach.ease.lsoft.com/archives/excel-g.html .
> >                              COPYRIGHT INFO:
> >
> http://peach.ease.lsoft.com/scripts/wa.exe?SHOWTPL=COPYRIGHT&L=EXCEL-G
> >
> ****** IMPORTANT NOTICE ****** Please note that we reserve
> the right to
> monitor and read any Emails sent and received by the Company under the
> Telecommunications (Lawful Business Practice) (Interception of
> Communications) Regulations 2000.
>
> --------------------------------------------------------------
> ------------
> The EXCEL-G list is hosted on a Windows NT(TM) machine running L-Soft
> international's LISTSERV(R) software.  For subscription/signoff info
> and archives, see http://peach.ease.lsoft.com/archives/excel-g.html .
>                              COPYRIGHT INFO:
> http://peach.ease.lsoft.com/scripts/wa.exe?SHOWTPL=COPYRIGHT&L=EXCEL-G
>
>
>
>
>
>
>
>
> This material has been checked by us for computer viruses and
> although none has been found by us, we cannot guarantee that
> it is completely free from such problems and we do not accept
> any liability for loss or damage which may be caused.  Please
> therefore check any attachments for viruses before using them
> on your own equipment.  If you do find a computer virus
> please inform us immediately so that we may take appropriate action.
>
> This communication is intended solely for the addressee and
> is confidential.
>
> If you are not the intended recipient, any disclosure,
> copying, distribution or any action taken or omitted to be
> taken in reliance on it, is prohibited and may be unlawful.
>
> Visit our website at http://www.hewittbaconwoodrow.co.uk (UK)
> or http://www.hewittbecketts.ie (Ireland)
>
> --------------------------------------------------------------
> ------------
> The EXCEL-G list is hosted on a Windows NT(TM) machine running L-Soft
> international's LISTSERV(R) software.  For subscription/signoff info
> and archives, see http://peach.ease.lsoft.com/archives/excel-g.html .
>                              COPYRIGHT INFO:
> http://peach.ease.lsoft.com/scripts/wa.exe?SHOWTPL=COPYRIGHT&L=EXCEL-G
>
****** IMPORTANT NOTICE ****** Please note that we reserve the right to
monitor and read any Emails sent and received by the Company under the
Telecommunications (Lawful Business Practice) (Interception of
Communications) Regulations 2000.

--------------------------------------------------------------------------
The EXCEL-G list is hosted on a Windows NT(TM) machine running L-Soft
international's LISTSERV(R) software.  For subscription/signoff info
and archives, see http://peach.ease.lsoft.com/archives/excel-g.html .
                             COPYRIGHT INFO:
http://peach.ease.lsoft.com/scripts/wa.exe?SHOWTPL=COPYRIGHT&L=EXCEL-G