Select the cells you want to convert and then run the macro.

Sub converttotext
for each cell in selection
v = cell.Text
cell.NumberFormat = "@"
cell.Value = "'" & cell.Text
Next
End Sub

--
Regards,
Tom Ogilvy

"Ann Mc" <Ann wrote in message
...
Tom
I believe this is the problem I have just been struggling with. Can you
tell me please how I can convert a large chunk of data already entered and
stored as numbers into text?
Thanks
Ann

"Tom Ogilvy" wrote:

Have you extended the lookup range to include the new data?

Often this is caused because you are looking for "123" and the source
range
is 123. The string "123" does not match the number 123 (regardless of
which
is the term being looked for and the which is in the source data).

If a cell is formatted as Text and you enter 123, you actually get "123"
If a cell contains the number 123 and you then format it as text, the
numer
123 is still stored in the cell.

Perhaps something like this is the problem.

--
Regards,
Tom Ogilvy


"charlene leblanc" <charlene wrote in
message ...
I have inherited two spreadsheets that are linked and a VLOOKUP in one
is
referring to a range in the other. The VLOOKUPs all work on the
existing
data but as when I add new data, bearing in mind the formatting of the
cells
and the order of the lookup range, those do not work. I get the #N/A
error.

Any ideas?