Jagwarman

asked on

# vlookup with MID formula and some spaces

Hello experts,

could someone provide me with a formula that will do a vlookup with a MID formula but where there are sometimes spaces.

i.e.

MD9 Look this up ME1 with no spaces

so my VLOOKUP would be something like:

=VLOOKUP(MID(C2,18,5),Sheet2!,J:K,2,0)

to return the number 123456

but where the cell has a space in this one: 'ME1 2'

MD9 Look this up ME1 2 with a space

It returns an error.

Thanks in advance

could someone provide me with a formula that will do a vlookup with a MID formula but where there are sometimes spaces.

i.e.

MD9 Look this up ME1 with no spaces

so my VLOOKUP would be something like:

=VLOOKUP(MID(C2,18,5),Shee

to return the number 123456

but where the cell has a space in this one: 'ME1 2'

MD9 Look this up ME1 2 with a space

It returns an error.

Thanks in advance

To clarify certain things please upload a sample excel file showing the working and not working cases

How about this?

=VLOOKUP(SUBSTITUTE(MID(C2,18,5)," ",""),Sheet2!,J:K,2,0)

The substitute function replaces the specified text with a new text, in this case will replace all spaces with nothing.

Syntax: =SUBSTITUTE(Text, Old Value, New Value,

=VLOOKUP(SUBSTITUTE(MID(C2

The substitute function replaces the specified text with a new text, in this case will replace all spaces with nothing.

Syntax: =SUBSTITUTE(Text, Old Value, New Value,

*Instance*)

ASKER

Can you give a more realistic example? I assume your real data doesn't say:

"MD9 Look this up ME1 with no spaces"

You obviously want to find a value to lookup within another string. What is the configuration of the real string? We can then give a way of finding the required string within it.

"MD9 Look this up ME1 with no spaces"

You obviously want to find a value to lookup within another string. What is the configuration of the real string? We can then give a way of finding the required string within it.

ASKER CERTIFIED SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

ASKER

I am looking up ME1 or ME2 or ME1 1 or ME2 2 or ME3 or ME3 2 etc

The given formula should work. Tell me if it does not. Also upload a sample file in this case.

ASKER

Sorry Saqib Husain, Syed been so busy at work and believe it or not I don't have access to internet from my PC so I have do test at home then copy code take to work type code in and test. It works great on my home PC will test it at work on Monday. Have a great weekend.

ASKER

Hi Saqib Husain, Syed tested and works perfectly. Many thanks and sorry for the delay.