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

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.

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

ADVERTISEMENT

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,137,060
Messages
5,679,382
Members
419,824
Latest member
Mercy kiara

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