# Problem with a String Search

#### TobiasTokio

##### Active Member
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

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.
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

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!

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

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

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)

Thanks a lot 4 your help!

Replies
2
Views
162
Replies
4
Views
2K
Replies
4
Views
250
Replies
6
Views
613
Replies
58
Views
4K

Threads
1,221,497
Messages
6,160,150
Members
451,626
Latest member
sukhman

### 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

### 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