Remember Me?

#1
April 29th 09, 06:29 PM posted to microsoft.public.excel.misc
 external usenet poster First recorded activity by ExcelBanter: Aug 2006 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(A11, 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
 external usenet poster First recorded activity by ExcelBanter: Nov 2006 Posts: 15,768
Function that can differentiate between text & numbers???

Try this:

=IF(ISTEXT(A1),A1,IF(COUNT(A1),IF(A11,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(A11, 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
 external usenet poster First recorded activity by ExcelBanter: Dec 2007 Posts: 2,722
Function that can differentiate between text & numbers???

Try this:

=IF(AND(ISNUMBER(A1),A11),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(A11, 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
 external usenet poster First recorded activity by ExcelBanter: Aug 2006 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),A11),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(A11, 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
 external usenet poster First recorded activity by ExcelBanter: Aug 2006 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(A11,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(A11, 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
 external usenet poster First recorded activity by ExcelBanter: Nov 2006 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(A11,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(A11, 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
 external usenet poster First recorded activity by ExcelBanter: Aug 2008 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(A11, 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

 Posting Rules Smilies are On [IMG] code is On HTML code is OffTrackbacks are On Pingbacks are On Refbacks are On

 Similar Threads Thread Thread Starter Forum Replies Last Post RodJ Excel Worksheet Functions 6 September 25th 08 08:24 AM Cardslinger New Users to Excel 6 June 14th 08 07:10 PM Natalie Excel Worksheet Functions 7 November 9th 05 09:50 PM Lettie Excel Worksheet Functions 6 March 22nd 05 09:47 AM John T. Sheedy Excel Worksheet Functions 2 March 8th 05 03:13 PM

All times are GMT +1. The time now is 10:37 AM.