Problem with a String Search

TobiasTokio

Active Member
Joined
Mar 30, 2006
Messages
262
Hey,

I have the following strings at differenet locations:
Code:
*   1 cash and cash equivalents
Code:
*  1 cash and cash equivalents
As you can see the differene between them is onlu one "space"!

I tried with VLOOKUP
Code:
=VLOOKUP(A3,Sheet1!AJ$3:AM$85,4,0)
and with SEARCH
Code:
=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
 

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.

venkat1926

Well-known Member
Joined
Aug 21, 2005
Messages
4,824
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

Active Member
Joined
Mar 30, 2006
Messages
262
Hey thanks 4 your help!

* is part of the string so I have
3 spaces after the "*" ins this string:
Code:
*   1 cash and cash equivalents

and 2 spaces after the "*" in that string:
Code:
*  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

Active Member
Joined
Mar 30, 2006
Messages
262
I thought SEARCH also searches for a part of a string why does it not work here?
 

Richard Schollar

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707
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

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
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)
 

Forum statistics

Threads
1,171,463
Messages
5,875,658
Members
433,145
Latest member
nzltrippa

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top