String equivalence

[Martina]

Dear experts,
I have a very frustrating problem

I download student numbers and associated data from two separate web-based
databases. One is a a student database(SourceA) and the other is
Blackboard(SourceB), more for student/staff communication.

The same student number from these two separate sources are not seen by
excel as being equivalent. Now I have searched on the web and have been down
the route of identifying, substituting char160 for char32 and removing the
first 0.

i.e.

SourceA: "0616025915 "
SourceB: "616025915"

=TRIM(SUBSTITUTE(H10,CHAR(160),CHAR(32)))
=MID(I10,2,LEN(I10))

Char160 sits on the end of the number from SourceA.
If I apply the first formula to SourceA and then manually delete the zero,
it works, I can then use VLOOKUPS successfully.
If I apply the first and the second formula to SourceA then VLOOKUPS do not
work.

My aim is not to do anything entry by entry but come up with a formula which
renders one format equivalent to the other so the two sets of data can be
merged.

Could anyone help one exasperated little black duck!!!
Martina

 

[JLatham]

Source B has the student IDs as numbers, Source A is numbers as text. Your
second formula, MID(I10,2,LEN(I10)) is retaining the text property of it even
though you stripped off the leading zero.
Try changing that formula to
=VALUE(MID(I10,2,LEN(I10)))
you may need to change the format of that column back to General. But with
those two changes, I believe you'll get the match needed.

 

[Steve Telford]

Give this a shot for SourceA:

=MID(H10,2,LEN(H10)-2)

This should remove the leading 0 and the trailing Char(160) character.

Hope this helps.

 

[Martina]

Oh my Gosh, I have been working on this for days! and going around in
circles. Thank you to both of you. JLatham, the combination of the two
formulae with the change you suggested did the trick. I would like to use
one formula and initially Steve yours did not work until I double clicked in
the result cell and then clicked in another cell. How bizarre!! Is there a
perfectly rational reason for this? Thanks again People Who Know How to Use
Excel
Martina

 

[Martina]

If I only use the first formula and double click in the resulting cell and
then click anywhere else ... It works. What is happening?

 

[Martina]

I see now that it is a number/text designation which is the final discrepancy
after I remove the char 160.

I can use my first formula:
=TRIM(SUBSTITUTE(H10,CHAR(160),CHAR(32)))

and then highlight them all and take advantage of the function under the
white error tag and convert them all to numbers (also achieved by double
clicking each cell?). I am then back in action.

Thanks so much.

Martina