Link to home
Start Free TrialLog in
Avatar of 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.


MD9 Look this up ME1 with no spaces

so my VLOOKUP would be something like:


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
Avatar of Saqib Husain
Saqib Husain
Flag of Pakistan image

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, Instance)
Avatar of Jagwarman


I have attached a file which hopefully makes it more clear.

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.
Avatar of Saqib Husain
Saqib Husain
Flag of Pakistan image

Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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.
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.
Hi Saqib Husain, Syed tested and works perfectly. Many thanks and sorry for the delay.