Problem with a String Search

[TobiasTokio]

Hey,

I have the following strings at differenet locations:

*   1 cash and cash equivalents

*  1 cash and cash equivalents

As you can see the differene between them is onlu one "space"!

I tried with VLOOKUP

=VLOOKUP(A3,Sheet1!AJ$3:AM$85,4,0)

and with SEARCH

=SEARCH(A3,Sheet1!AJ:AJ,4)

to get some amounts next to these strings but it doesn't work.

Maybe you can help me!
Thanks
Tobi

 

[venkat1926]

what do you want to do. do yoy want to be exactly same.
is * part of the string.

if there are only leading spaces( and no non printing characters) then you can use TRIM functon.
see help
supposse the srint is in a1 and a2
type in B1
=trim(a1)
and enter
copy this to B2.
see whether both are exact

 

[TobiasTokio]

Hey thanks 4 your help!

* is part of the string so I have
3 spaces after the "*" ins this string:

*   1 cash and cash equivalents

and 2 spaces after the "*" in that string:

*  1 cash and cash equivalents

What I wanna do is: to look after this "1 cash and cash equivalents" string in another workbook and get the number which is in the cell next to it!

 

[TobiasTokio]

I thought SEARCH also searches for a part of a string why does it not work here?

 

[Richard Schollar]

Hi Tobias

You can use wildcards in VLOOKUPs so you might be able to use something like this:

=VLOOKUP("*"&MID(A3,3,25)&"*",Sheet1!AJ$3:AM$85,4,0)

You can play around with the MID arguments to tailor it to your needs.

Hope this helps!

Richard

 

[Andrew Poulsom]

SEARCH expects a single cell as its within_text argument and returns the position at which find_text is first found. It's not the same as VLOOKUP.

Try:

=VLOOKUP(TRIM(SUBSTITUTE(A3,"*","")),Sheet1!AJ$3:AM$85,4,0)

 

[TobiasTokio]

Thanks a lot 4 your help!