Excel: lookup problems with mixed alpha/numeric data

[sagobogger]

Here's a problem that I usually have to spend a lot of time to fix. I need to create a lookup between two big sets of data from different sources. The lookup column contains both pure numerics and mixed alphanumerics, e.g.

10021
10022
1002F
V5364

Murphy's law usually dictates that the fields in one list will have numeric and alphanumeric vales all formatted as text, and the other list will have the numerics as numbers, and the alphanumerics as text, so they won't work directly in a lookup.

Is there some formula I can apply to all cells in both lists to get them to a common format (in a new column) that will work in a lookup? e.g. I can use VALUE to convert the text numerics to real numbers, but that will fail #VALUE! on the alpha fields.

 

[SkipVought]

Your "numbers" in this column will NEVER be used to calculate anything, I'd wager. They are IDENTIFIERS, and as such, ought to be converted to TEXT.

FORMAT does NOTHING to the underlying value. You must CONVERT everything -- lookup values and lookup table. I usually use a leading TIC '

Skip,

_{When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large}

 

[AnotherHiggins]

->Is there some formula I can apply to all cells in both lists to get them to a common format

As Skip said, you can lead off the cell with a single quote to tell Excel that what comes after is text. To do this formulaicly (for a large amount of existing data), you can just concatenate the ' with the data in the cell.

For example, if your data is in column A, then in another column you could use:
[tab]="'" & A2
then fill the formula down

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]



Help us help you. Please read FAQ 181-2886 before posting.

 

[Welshbird]

Does =text(ref) work too?

(Am not sure and don't have excel at home to try)

Fee

The question should be [red]Is it worth trying to do?[/red] not [blue] Can it be done?[/blue]

 

[SkipVought]

Tha may work on the LOOKUP VALUE but not on the LOOKUP RANGE.

Skip,

_{When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large}

 

[AnotherHiggins]

Well I suppose you could make the whole thing an array formula:

=VLOOKUP(TEXT(A1,"@"),TEXT($B$1:$B$4,"@"),1,0)

But instead of just pressing enter after you type it in, you must press [Ctrl]+[Shift]+[Enter]. When done correctly, you will see curly brackets - {} - around the formula in the formula bar.

But I suspect that would take up a lot of overhead if done on large datasets.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]



Help us help you. Please read FAQ 181-2886 before posting.

 

[SkipVought]

Hmmmmm. Mrs. Charles, Leonard & Stevens would say, sing a song, take a punch, have a laugh!

Rachael could cook up a storm.

Or, if you take the sting out of that hotrod name...

...Allllll's left is array.

I'm Thankful!

Skip,

_{When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large}

 

[sagobogger]

Thanks for the ideas folks. Adding the leading ' resulted in it being added to the field value, so 12345 displayed as '12345. With that clue I realized I was wrong about the numeric/text format mix. Everything in both data sets is text, but one set right justifies the numbers and the leading spaces were causing the mismatch. Trim() solves the problem

Always helps to talk to someone...

 

[AnotherHiggins]

Skip, Have you started drinking already??

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]



Help us help you. Please read FAQ 181-2886 before posting.