Combing Find and Functions

[Marg]

Version: 2008
Operating System: Mac OS X 10.5 (Leopard)
Processor: Intel

Hi -

What I want to do is have Excel look through cells which have both text and numbers, and if a specific number is present, either place that number in another cell, or at least assign a specified colour to each such cell. What I'm doing is pulling out conversion rates for eBay sales, and so far haven't been able to figure out how to formularize the work. This means sorting by date and eyeballing each transaction description to put the conversion rate into its own column where it can operate as a number.

Any suggestions would be very much appreciate.

 

[Carl Witthoft]

Version: 2008
Operating System: Mac OS X 10.5 (Leopard)
Processor: Intel

Hi -

What I want to do is have Excel look through cells which have both text and
numbers, and if a specific number is present, either place that number in
another cell, or at least assign a specified colour to each such cell. What
I'm doing is pulling out conversion rates for eBay sales, and so far haven't
been able to figure out how to formularize the work. This means sorting by
date and eyeballing each transaction description to put the conversion rate
into its own column where it can operate as a number.

Any suggestions would be very much appreciate.

Coloring the cell is done via the "Conditional Formatting" dialog box,
and I think you can pretty much stick an IF(FIND()) in there.

To put the number elsewhere, I think you'll have to do similarly:

=IF(FIND("17",A1),17,"")
But of course this only works if the number you're looking for is
exactly 17. You can extend this using TEXT(INDIRECT()) to plop the
number from some other cell in place of the 17 I put there, but notice
in any case you need to use the quotes because '17' is part of a text
string in the cell you're searching.

 

[Marg]

Hi, Carl,

Thank you very much for the suggestions. This should work, and make the task easier.

Marg

Version: 2008
Operating System: Mac OS X 10.5 (Leopard)
Processor: Intel

Hi -

What I want to do is have Excel look through cells which have both text and
numbers, and if a specific number is present, either place that number in
another cell, or at least assign a specified colour to each such cell. What
I'm doing is pulling out conversion rates for eBay sales, and so far haven't
been able to figure out how to formularize the work. This means sorting by
date and eyeballing each transaction description to put the conversion rate
into its own column where it can operate as a number.

Any suggestions would be very much appreciate.

Coloring the cell is done via the "Conditional Formatting" dialog box,
and I think you can pretty much stick an IF(FIND()) in there.

To put the number elsewhere, I think you'll have to do similarly:

=IF(FIND("17",A1),17,"")
But of course this only works if the number you're looking for is
exactly 17. You can extend this using TEXT(INDIRECT()) to plop the
number from some other cell in place of the 17 I put there, but notice
in any case you need to use the quotes because '17' is part of a text
string in the cell you're searching.
[/QUOTE]

 

[Shane Devenshire]

Hi,

Are you trying to find the a specific number in any cell that contains text
or numbers, if so, conditional formatting may be your best choice. Assume
your range is A1:Z1000.

1. Highlight all the cell you want to look at and choose Format, Conditional
Formatting, and from the first drop down pick Formula is
2. In the box to the right enter the formula:
=FIND(17,A1)
3. Click Format and choose a format.

This will highlight all the cells containing 17

 

[Marg]

Thank you, Shane, this also looks very useful. I'm not sure what you mean by the Yes button, but it definitely helps!
Marg

Hi,

Are you trying to find the a specific number in any cell that contains text
or numbers, if so, conditional formatting may be your best choice. Assume
your range is A1:Z1000.

1. Highlight all the cell you want to look at and choose Format, Conditional
Formatting, and from the first drop down pick Formula is
2. In the box to the right enter the formula:
=FIND(17,A1)
3. Click Format and choose a format.

This will highlight all the cells containing 17

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire

 

[Carl Witthoft]

Marg

Marg, Shane is having fun at the expense of newbies like yourself .

Try Googling for "Hit the Any Key" for similar fun and games.

 

[CyberTaz]

Marg, Shane is having fun at the expense of newbies like yourself .

Try Googling for "Hit the Any Key" for similar fun and games.

Not really, Carl Note Shane's posting address... he's using the MS Forum
UI [discussions.microsoft.com] which does in fact have such a button. The
button actually doesn't do anything other than to cause a green check to
appear indicating that someone considered the reply "helpful". It's my
understanding that nobody from MS actually tracks the check marks & they
haven't really meant much for years. Also, some folks [I'm not saying this
is true of Shane] seem to have the impression that receiving a certain
number of "helpful" button-pushes earns them some sort of recognition from
MS.

 

[Phillip Jones, C.E.T.]

Oh, Oh! He/she has been visiting the new JIVE based Adobe Forums where
you give points for helpful and Correct Points. Click help for a given
answer the supposed helpful person get 5 points, answered, even if
according to the experts is incorrect get 1 points. The regulars there
hate the idea think its silly. They've even started thread just to rack
up points, Just so Adobe/Jive will figure out how dumb it is.

Marg, Shane is having fun at the expense of newbies like yourself .

Try Googling for "Hit the Any Key" for similar fun and games.

Not really, Carl Note Shane's posting address... he's using the MS
Forum UI [discussions.microsoft.com] which does in fact have such a
button. The button actually doesn't do anything other than to cause a
green check to appear indicating that someone considered the reply
"helpful". It's my understanding that nobody from MS actually tracks the
check marks & they haven't really meant much for years. Also, some folks
[I'm not saying this is true of Shane] seem to have the impression that
receiving a certain number of "helpful" button-pushes earns them some
sort of recognition from MS.

 

[Phillip Jones, C.E.T.]

Actually that's supposed to be 10 points correct answer not 1. They
goes that proofreading bug *I* have. :-(

Oh, Oh! He/she has been visiting the new JIVE based Adobe Forums where
you give points for helpful and Correct Points. Click help for a given
answer the supposed helpful person get 5 points, answered, even if
according to the experts is incorrect get 1 points. The regulars there
hate the idea think its silly. They've even started thread just to rack
up points, Just so Adobe/Jive will figure out how dumb it is.

Thank you, Shane, this also looks very useful. I'm not sure what
you > mean
by the Yes button, but it definitely helps!

Marg


Marg, Shane is having fun at the expense of newbies like yourself .

Try Googling for "Hit the Any Key" for similar fun and games.

Not really, Carl Note Shane's posting address... he's using the MS
Forum UI [discussions.microsoft.com] which does in fact have such a
button. The button actually doesn't do anything other than to cause a
green check to appear indicating that someone considered the reply
"helpful". It's my understanding that nobody from MS actually tracks
the check marks & they haven't really meant much for years. Also, some
folks [I'm not saying this is true of Shane] seem to have the
impression that receiving a certain number of "helpful" button-pushes
earns them some sort of recognition from MS.