We help IT Professionals succeed at work.

vlookup with MID formula and some spaces

Jagwarman
Jagwarman asked
on
1,121 Views
Last Modified: 2016-07-29
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
Comment
Watch Question

Saqib HusainEngineer
CERTIFIED EXPERT

Commented:
To clarify certain things please upload a sample excel file showing the working and not working cases
Rob HensonFinance Analyst
CERTIFIED EXPERT

Commented:
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)

Author

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

Thanks
lookup.xls
Rob HensonFinance Analyst
CERTIFIED EXPERT

Commented:
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.
Engineer
CERTIFIED EXPERT
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
I am looking up ME1 or ME2 or ME1 1 or ME2 2 or ME3 or ME3 2 etc
Saqib HusainEngineer
CERTIFIED EXPERT

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

Author

Commented:
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.

Author

Commented:
Hi Saqib Husain, Syed tested and works perfectly. Many thanks and sorry for the delay.
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.