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.

 Function that can differentiate between text & numbers???
 Site Map Home Register Authors List Search Today's Posts Mark Forums Read Web Partners

## Function that can differentiate between text & numbers???

#1
April 29th 09, 06:29 PM posted to microsoft.public.excel.misc
 Kevin external usenet poster Posts: 504
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?
Kevin
#2
April 29th 09, 06:41 PM posted to microsoft.public.excel.misc
 T. Valko external usenet poster Posts: 15,768
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?
> Kevin

#3
April 29th 09, 06:44 PM posted to microsoft.public.excel.misc
 Luke M external usenet poster Posts: 2,722
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?
> Kevin

#4
April 29th 09, 07:00 PM posted to microsoft.public.excel.misc
 Kevin external usenet poster Posts: 504
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?
> > Kevin

#5
April 29th 09, 07:01 PM posted to microsoft.public.excel.misc
 Kevin external usenet poster Posts: 504
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?
> > Kevin

>
>
>

#6
April 29th 09, 07:08 PM posted to microsoft.public.excel.misc
 T. Valko external usenet poster Posts: 15,768
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?
>> > Kevin

>>
>>
>>

#7
April 29th 09, 08:40 PM posted to microsoft.public.excel.misc
 Rick Rothstein external usenet poster Posts: 5,934
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?
> Kevin

 Thread Tools Display Modes Linear Mode

 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 User Control Panel Private Messages Subscriptions Who's Online Search Forums Forums Home Excel Newsgroups     Excel Discussion (Misc queries)     Setting up and Configuration of Excel     New Users to Excel     Excel Worksheet Functions     Links and Linking in Excel     Charts and Charting in Excel     Excel Programming About ExcelBanter     About this forum

 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 02:21 AM.