I gang

Excel function to lookup sub-strings in a string

I need a function for Excel (Version 2007 and higher) that does the following:

Check the string in a given cell in the same worksheet (e.g. cell A1) for instances of strings listed in a separate worksheet (called here: LUStreets) in column A. [This is, so far, what VLOOKUP with “range_lookup=TRUE” would do).]

If the string is found at any (!!!) position in the string in A1 (be it at the beginning*, be it in the middle or the end), the function pastes a string taken from LUStreets column B. [This exceeds what VLOOKUP can do, especially with respect to “at any position”].

---- Example: ----

A1:

Neustadt, WBZ, Goethestr. 11/13 , R 105 Computerraum

LUStreets

A B

Goethestr Goethestraße 11-13

Blumenw Blumenweg 53

Washingtonp Washingtonplatz 2

Fritz-Müller-Str Fritz-Müller-Straße 28

Brauereigasse Brauereigasse 15

Brauerei-Gasse Brauereigasse 15

Output:

Goethestraße 11-13

------ End of the example ------

I would like to have a function, not a formula, to more easily use it whenever needed.

The function will have a number of parameters, at least:

lookupstring(Cell where to check; Range with lookup values)

Additional feature (if possible): Lookup case sensitive / non case sensitive [would be a third parameter]

Attention, I am using English Excel, but with German language settings, so semicolon is used in stead of comma to separate parameters.

Example: FIND("Washington";A1;5)

Please include also the following requirement:

When no match has been found, an empty string ("") will be the output, unless I set a deliberate other string in the function's parameters.

The parameters will be, therefore:

lookupstrings(Where to check; Range with lookup values; [output if no match was found]; [switch on/off case sensitive])

Færdigheder: Visual Basic for Apps

Se mere: string find c, string b, number function, function number, a b, Vlookup, strings, str, semicolon, LOOkup, excel vlookup, excel function, excel formula, string find, settings visual basic language, excel additional, english worksheet, excel check, applications excel, lookup excel, number lookup, vlookup excel, additional function, excel english german, use lookup function mysql

Om arbejdsgiveren:
( 34 bedømmelser ) Munich, Germany

Projekt-ID: #4083869

Tildelt til:

bpascal123

Hi Chgeiselmann It's something I can do. I can write script using vba for macros or write user defined functions with vba. Regards Pascal Baro

$30 USD på 1 dag
(5 bedømmelser)
2.0

5 freelancere byder i gennemsnit $78 for dette job

eugene2006

Hi. I interested in your project.

$30 USD in 3 dage
(1 bedømmelse)
2.8
ebizworld1

I am really interested in your project, let me do it for you. thanks

$250 USD in 5 dage
(0 bedømmelser)
0.0
anomanpk

I am really interested in your project. Please see PM for details.

$50 USD in 3 dage
(0 bedømmelser)
0.0
Nirajbajaniya

i really do this projects i hope you may give me a positive rpelay.

$45 USD in 3 dage
(0 bedømmelser)
0.0
ryanbct

Hi There, This function is easier said that done. I have done an experiment to see how long it will take to create such a function. I don't think that it should take longer than 3 hours for all your requirements. Mere

$30 USD på 1 dag
(0 bedømmelser)
0.0