Methods for making numbers into text are not the same

  • Thread starter Thread starter Tuck D.
  • Start date Start date
T

Tuck D.

Entering a number into a cell that is formatted as "general", and then
changing the format to "text" is not the same as formatting a blank cell as
"text" and then entering a number. In the former, it is considered text, but
in the later method it is deemed a number but in text format. Also, in the
former case, entering a new number into the cell will change it to being a
number in text format.

The problem comes in doing a "match" function. The the lookup array needs
to have been formatted using the same method as the lookup value in order for
there to be a match.

Example:
I have a list of tasks that are numbered like:
4.8
4.9
4.10
4.11
These need to be a text format so that the subsection numbers are displayed
correctly.
The lookup value is a reference to a cell that has been formatted as text.
I.e. MATCH(B12, A1:A4)
If cell B12 was not formatted in the same manner as its corresponding value
in column A, then there is no match.

Any suggestions? (I will need to change values often, and I don't want to go
to a "4.07" style listing.
 
If A1:A4 are all text values and B12 is a number or text:

=MATCH(B12&"", A1:A4, 0)
(0 means an exact match)

If A1:A4 are all numbers and B12 may be a number or text:
=MATCH(--B12, A1:A4, 0)

If A1:A4 can be a mixture of text and numbers and B12 can be either a number or
text:

The values have to look like numbers:
=match(--b12, --(a1:a4),0)
or
The values can contain strings that don't look like numbers:
=match(b12&"", a1:a4&"",0)

This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it
correctly, excel will wrap curly brackets {} around your formula. (don't type
them yourself.)
 
Back
Top