Sedang Disiapkan

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)

Kemahiran: Visual Basic untuk Aplikasi

Lihat lebih lanjut: string b, number function, function number, a b, 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, excel vlookup formula, worksheet function

Tentang Majikan:
( 34 ulasan ) Munich, Germany

ID Projek: #4083869

Dianugerahkan kepada:

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 dalam sehari
(5 Ulasan)
2.0

5 pekerja bebas membida secara purata $78 untuk pekerjaan ini

eugene2006

Hi. I interested in your project.

$30 USD dalam 3 hari
(1 Ulasan)
2.8
ebizworld1

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

$250 USD dalam 5 hari
(0 Ulasan)
0.0
anomanpk

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

$50 USD dalam 3 hari
(0 Ulasan)
0.0
Nirajbajaniya

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

$45 USD dalam 3 hari
(0 Ulasan)
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. Lagi

$30 USD dalam sehari
(0 Ulasan)
0.0