Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 64
Default Convertig Number to Text - equivelent to single quote

I have a list of item numbers that are sent to me from different sources -
sometimes formatted as number and sometimes formatted as text. The master
file I keep for lookup formaula has the items stored as text - with my
problem being the VLOOKUP formula expecting the values to match, and is not
giving results all the time.

To cure the issue I can go to the numeric value and type a single quote
inthe cell preceding the number and this converts it to text and the lookups
work, but sometimes the list is hundreds of items long, so I am looking for
an easier way to do this. Simply highligting the column and formatting all
values as text does not do the trick - is there a formula that will work that
I can apply to the cells? So far I have not had luck with the TEXt and T
formulas.

Thanks
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 47
Default Convertig Number to Text - equivelent to single quote

Copy a blank cell, then highlight your data, right click Paste Special,
select Add then click OK

----------------
mama no teeth

"Nathan" wrote:

I have a list of item numbers that are sent to me from different sources -
sometimes formatted as number and sometimes formatted as text. The master
file I keep for lookup formaula has the items stored as text - with my
problem being the VLOOKUP formula expecting the values to match, and is not
giving results all the time.

To cure the issue I can go to the numeric value and type a single quote
inthe cell preceding the number and this converts it to text and the lookups
work, but sometimes the list is hundreds of items long, so I am looking for
an easier way to do this. Simply highligting the column and formatting all
values as text does not do the trick - is there a formula that will work that
I can apply to the cells? So far I have not had luck with the TEXt and T
formulas.

Thanks

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
How to set a number number value as text in all cells ? Paul Peters Excel Worksheet Functions 0 August 11th 06 07:27 AM
number range returning a text value jason New Users to Excel 3 June 15th 06 08:09 PM
Return SEARCHED Column Number of Numeric Label and Value Sam via OfficeKB.com Excel Worksheet Functions 23 January 30th 06 07:16 PM
Excell-How to add the number of cells containing text? = a sum aruba64taw Excel Worksheet Functions 2 December 21st 05 10:51 AM
How to I copy text from a range of cells to another single cell? WRT Excel Discussion (Misc queries) 2 December 18th 05 07:17 AM


All times are GMT +1. The time now is 11:41 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"