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
Microsoft Excel

Avatar of undefined
Last Comment

8/22/2022 - Mon
Saqib Husain

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

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)

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

Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
Rob Henson

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.
Saqib Husain

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question

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

The given formula should work. Tell me if it does not. Also upload a sample file in this case.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.

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.