Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
 
Posts: n/a
Default application exports number to excels text....can't format

Hi,

I have an app that sends the results of some number crunching as text.
I need them to be number or currency for further calculations. I've
tried formating cells, I've tried using the currency button, makes no
difference. An apostrophy is visible at the beginning of the number,
and of course I could delete it manually, then convert to text. But
with each workbook containing several thousand lines, this isn't
sustainable. "Find" doesn't find the apostrophy.

Ideas ?

  #2   Report Post  
Posted to microsoft.public.excel.misc
Elkar
 
Posts: n/a
Default application exports number to excels text....can't format

1. In a blank cell, enter 0
2. Copy that cell
3. Select your range of data
4. "Paste Special" and select "Add"

This should solve your problem.

HTH,
Elkar

" wrote:

Hi,

I have an app that sends the results of some number crunching as text.
I need them to be number or currency for further calculations. I've
tried formating cells, I've tried using the currency button, makes no
difference. An apostrophy is visible at the beginning of the number,
and of course I could delete it manually, then convert to text. But
with each workbook containing several thousand lines, this isn't
sustainable. "Find" doesn't find the apostrophy.

Ideas ?


  #3   Report Post  
Posted to microsoft.public.excel.misc
Pete
 
Posts: n/a
Default application exports number to excels text....can't format

I have come across situations where the cell is formatted as text and
the content is something like ''123 (i.e. double apostrophe, but shows
up as '123). Here, you have to use a compound formula in the helper
column, as follows:

=VALUE(RIGHT(A1,LEN(A1)-1))

Hope this helps.

Pete

  #4   Report Post  
Posted to microsoft.public.excel.misc
 
Posts: n/a
Default application exports number to excels text....can't format

Thanks for that, it works well.

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
Convert numbers from text format to number format merlin68 Excel Discussion (Misc queries) 7 June 20th 07 07:03 PM
" / " Changing Decimal number format to Fraction on Protected Cell laudie Excel Worksheet Functions 0 November 15th 05 02:12 AM
Format Number to Text Roni Excel Worksheet Functions 2 May 17th 05 03:17 PM
Number is in a text format dbl Excel Worksheet Functions 1 February 19th 05 09:57 PM
convert number to text and format it. Goda Excel Worksheet Functions 2 February 7th 05 05:37 PM


All times are GMT +1. The time now is 12:40 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"