Solved How can i edit characters within a string of characters?

February 2, 2017 at 06:27:51
Specs: Windows 7
I have two excel spreadsheet that I need to compare using vlookup, using case number to do so. The two sheets have case number stored differently, for example, one has the, displayed as 2012DT00001 or 2001CM00012 and the other sheet displays them as 2012DT1 or 2001CM12. Obviously, they are the same record but when using vlookup to compare the record and bring over information that is on one and not the other it does not work well. I have tried using vlookup to compare and look for approximate matches (TRUE). However, when I look at a sampling of the information it misses 80% of the records. At this point, I am thinking that the vlookup function is missing those records as a result of the two case numbers being different as a result of the unneeded zeros in the one. Any help with this would be greatly appreciated.

See More: How can i edit characters within a string of characters?

Report •

#1
February 2, 2017 at 07:38:39
✔ Best Answer
Here are two ways to do it:

With your data in Column A:

        A          B
1) 2012DT00001 	2012DT1
2) 2001CM00012	2001CM12

In cell B1 enter the formula: =LEFT(A1,6)&VALUE(RIGHT(A1,5))
Drag down as needed.

You can now use Column B in your =VLOOKUP()
something along the lines of:

=VLOOKUP(B1,Sheet2!A1:B2,2,1)

The second way is to use the formula directly in your =VLOOKUP()
which eliminates the need for column B

Something like:

=VLOOKUP(LEFT(A1,6)&VALUE(RIGHT(A1,5)),Sheet2!A1:B2,2,1)

The trick is in the: VALUE(RIGHT(A1,5)) section,
it takes the last five characters of your text string,
IE 00001 or 00012
and converts them from TEXT to a NUMBER,
and since Excel strips out any leading zeros in a number
you end up with your required digit.

See how that works for you.

MIKE

http://www.skeptic.com/


Report •

#2
February 2, 2017 at 14:35:04
This worked great! I used the =LEFT(A1,6)&VALUE(RIGHT(A1,5)) as you suggested from there use the VLOOKUP as previously described. I was able to connect and add information to thousands of records that was previously missing. Thank you!

Report •
Related Solutions


Ask Question