A Microsoft Excel forum. ExcelBanter

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » ExcelBanter forum » Excel Newsgroups » Excel Discussion (Misc queries)
Site Map Home Register Authors List Search Today's Posts Mark Forums Read Web Partners

Function that can differentiate between text & numbers???



 
 
Thread Tools Display Modes
  #1  
Old April 29th 09, 06:29 PM posted to microsoft.public.excel.misc
Kevin
external usenet poster
 
Posts: 504
Default Function that can differentiate between text & numbers???

I have a spreadspread that contains both text and numbers in every column and
row. The values are in euro. I usually copy the spreadsheet over and multiply
the values by the exchange rate.
Example:
88 98 n/a 50
78 n/a 45 51
I have tried using the IF function, IF(A1>1, A1*1.35, A1), but this returns
the #VALUE symbol in the cells that contain words.
Is there function that would allow me to copy it throughout the whole
spreadsheet, returning new values for cells with numbers and leaving all
cells with text exactly the way are?
Thank you for any help you could provide!!
Kevin
Ads
  #2  
Old April 29th 09, 06:41 PM posted to microsoft.public.excel.misc
T. Valko
external usenet poster
 
Posts: 15,768
Default Function that can differentiate between text & numbers???

Try this:

=IF(ISTEXT(A1),A1,IF(COUNT(A1),IF(A1>1,A1*1.35,A1) ,""))

--
Biff
Microsoft Excel MVP


"Kevin" > wrote in message
...
>I have a spreadspread that contains both text and numbers in every column
>and
> row. The values are in euro. I usually copy the spreadsheet over and
> multiply
> the values by the exchange rate.
> Example:
> 88 98 n/a 50
> 78 n/a 45 51
> I have tried using the IF function, IF(A1>1, A1*1.35, A1), but this
> returns
> the #VALUE symbol in the cells that contain words.
> Is there function that would allow me to copy it throughout the whole
> spreadsheet, returning new values for cells with numbers and leaving all
> cells with text exactly the way are?
> Thank you for any help you could provide!!
> Kevin



  #3  
Old April 29th 09, 06:44 PM posted to microsoft.public.excel.misc
Luke M
external usenet poster
 
Posts: 2,722
Default Function that can differentiate between text & numbers???

Try this:

=IF(AND(ISNUMBER(A1),A1>1),A1*1.35,A1)
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Kevin" wrote:

> I have a spreadspread that contains both text and numbers in every column and
> row. The values are in euro. I usually copy the spreadsheet over and multiply
> the values by the exchange rate.
> Example:
> 88 98 n/a 50
> 78 n/a 45 51
> I have tried using the IF function, IF(A1>1, A1*1.35, A1), but this returns
> the #VALUE symbol in the cells that contain words.
> Is there function that would allow me to copy it throughout the whole
> spreadsheet, returning new values for cells with numbers and leaving all
> cells with text exactly the way are?
> Thank you for any help you could provide!!
> Kevin

  #4  
Old April 29th 09, 07:00 PM posted to microsoft.public.excel.misc
Kevin
external usenet poster
 
Posts: 504
Default Function that can differentiate between text & numbers???

This is exactly what I have been trying to do. It worked perfectly! You have
saved me hours of work!! Thank you!!

"Luke M" wrote:

> Try this:
>
> =IF(AND(ISNUMBER(A1),A1>1),A1*1.35,A1)
> --
> Best Regards,
>
> Luke M
> *Remember to click "yes" if this post helped you!*
>
>
> "Kevin" wrote:
>
> > I have a spreadspread that contains both text and numbers in every column and
> > row. The values are in euro. I usually copy the spreadsheet over and multiply
> > the values by the exchange rate.
> > Example:
> > 88 98 n/a 50
> > 78 n/a 45 51
> > I have tried using the IF function, IF(A1>1, A1*1.35, A1), but this returns
> > the #VALUE symbol in the cells that contain words.
> > Is there function that would allow me to copy it throughout the whole
> > spreadsheet, returning new values for cells with numbers and leaving all
> > cells with text exactly the way are?
> > Thank you for any help you could provide!!
> > Kevin

  #5  
Old April 29th 09, 07:01 PM posted to microsoft.public.excel.misc
Kevin
external usenet poster
 
Posts: 504
Default Function that can differentiate between text & numbers???

Thank you. This worked great!!

"T. Valko" wrote:

> Try this:
>
> =IF(ISTEXT(A1),A1,IF(COUNT(A1),IF(A1>1,A1*1.35,A1) ,""))
>
> --
> Biff
> Microsoft Excel MVP
>
>
> "Kevin" > wrote in message
> ...
> >I have a spreadspread that contains both text and numbers in every column
> >and
> > row. The values are in euro. I usually copy the spreadsheet over and
> > multiply
> > the values by the exchange rate.
> > Example:
> > 88 98 n/a 50
> > 78 n/a 45 51
> > I have tried using the IF function, IF(A1>1, A1*1.35, A1), but this
> > returns
> > the #VALUE symbol in the cells that contain words.
> > Is there function that would allow me to copy it throughout the whole
> > spreadsheet, returning new values for cells with numbers and leaving all
> > cells with text exactly the way are?
> > Thank you for any help you could provide!!
> > Kevin

>
>
>

  #6  
Old April 29th 09, 07:08 PM posted to microsoft.public.excel.misc
T. Valko
external usenet poster
 
Posts: 15,768
Default Function that can differentiate between text & numbers???

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"Kevin" > wrote in message
news
> Thank you. This worked great!!
>
> "T. Valko" wrote:
>
>> Try this:
>>
>> =IF(ISTEXT(A1),A1,IF(COUNT(A1),IF(A1>1,A1*1.35,A1) ,""))
>>
>> --
>> Biff
>> Microsoft Excel MVP
>>
>>
>> "Kevin" > wrote in message
>> ...
>> >I have a spreadspread that contains both text and numbers in every
>> >column
>> >and
>> > row. The values are in euro. I usually copy the spreadsheet over and
>> > multiply
>> > the values by the exchange rate.
>> > Example:
>> > 88 98 n/a 50
>> > 78 n/a 45 51
>> > I have tried using the IF function, IF(A1>1, A1*1.35, A1), but this
>> > returns
>> > the #VALUE symbol in the cells that contain words.
>> > Is there function that would allow me to copy it throughout the whole
>> > spreadsheet, returning new values for cells with numbers and leaving
>> > all
>> > cells with text exactly the way are?
>> > Thank you for any help you could provide!!
>> > Kevin

>>
>>
>>



  #7  
Old April 29th 09, 08:40 PM posted to microsoft.public.excel.misc
Rick Rothstein
external usenet poster
 
Posts: 5,934
Default Function that can differentiate between text & numbers???

Here is a shorter formula for you to try...

=IF(N(A1)=0,A1,1.35*A1)

--
Rick (MVP - Excel)


"Kevin" > wrote in message
...
>I have a spreadspread that contains both text and numbers in every column
>and
> row. The values are in euro. I usually copy the spreadsheet over and
> multiply
> the values by the exchange rate.
> Example:
> 88 98 n/a 50
> 78 n/a 45 51
> I have tried using the IF function, IF(A1>1, A1*1.35, A1), but this
> returns
> the #VALUE symbol in the cells that contain words.
> Is there function that would allow me to copy it throughout the whole
> spreadsheet, returning new values for cells with numbers and leaving all
> cells with text exactly the way are?
> Thank you for any help you could provide!!
> Kevin


 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
How to differentiate groups of numbers RodJ Excel Worksheet Functions 6 September 25th 08 08:24 AM
Using a function with numbers & text Cardslinger New Users to Excel 6 June 14th 08 07:10 PM
How do I set up an IF function with text and numbers? Natalie Excel Worksheet Functions 7 November 9th 05 08:50 PM
How do I use COUNTIF in a SUBTOTAL function to differentiate the . Lettie Excel Worksheet Functions 6 March 22nd 05 08:47 AM
numbers to text function John T. Sheedy Excel Worksheet Functions 2 March 8th 05 02:13 PM


All times are GMT +1. The time now is 09:49 AM.


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