Finding 4600

  • Thread starter Thread starter Pinda
  • Start date Start date
P

Pinda

@vZ(PrintSMPCT-FS01úO /ýCFå;+|111Priority
Practice Analysis.xlsSarahTHP Color 4600
FalklandIP_62.130.88.322297110dLfLe¹f–?f–?

I have rows and rows of data similar to this in a
spreadsheet. the above data is within 1 cell. I need to
create a lookup that returns all the rows with '4600' in
the. The trouble is that the 4600 can be anywhere within
the text string.

Any ideas?
Thanks in advanvce.
 
Good morning Pinda-

You may find SEARCH helpful.
An example:

=search("4600",<cellref>)
This will return a number that is the position of the starting character in this case the 4.

That tells you the string 4600 is in the target cell, what you do with it - you have not indicated.

Search can be imbeded in other formulas like most other functions.

Let me know if you need further help or information concerning this issue.

Thanks,
Jon Barchenger
--------------------
**Content-Class: urn:content-classes:message
**From: "Pinda" <[email protected]>
**Sender: "Pinda" <[email protected]>
**Subject: Finding 4600
**Date: Tue, 18 Nov 2003 01:31:15 -0800
**Lines: 13
**Message-ID: <[email protected]>
**MIME-Version: 1.0
**Content-Type: text/plain;
** charset="iso-8859-1"
**Content-Transfer-Encoding: quoted-printable
**X-Newsreader: Microsoft CDO for Windows 2000
**X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4910.0300
**Thread-Index: AcOttrZwGannM3QIQ5icxdyf1oFleA==
**Newsgroups: microsoft.public.excel.worksheet.functions
**Path: cpmsftngxa06.phx.gbl
**Xref: cpmsftngxa06.phx.gbl microsoft.public.excel.worksheet.functions:171984
**NNTP-Posting-Host: TK2MSFTNGXA08 10.40.1.160
**X-Tomcat-NG: microsoft.public.excel.worksheet.functions
**
**@vZ(PrintSMPCT-FS01úO /ýCFå;+|111Priority
**Practice Analysis.xlsSarahTHP Color 4600
**FalklandIP_62.130.88.322297110dLfLe¹f–?f–?
**I have rows and rows of data similar to this in a
**spreadsheet. the above data is within 1 cell. I need to
**create a lookup that returns all the rows with '4600' in
**the. The trouble is that the 4600 can be anywhere within
**the text string.
**Any ideas?
**Thanks in advanvce.
**
 
thanks for that.

As you can see from the data, each row of data is
identifier for who has been printing to what printer. I
want to flag 4600 because they are colour so I can
determine whats being printed. Can you think of the
easiest way that i could flag up this data?

Many thanks
-----Original Message-----
Good morning Pinda-

You may find SEARCH helpful.
An example:

=search("4600",<cellref>)
This will return a number that is the position of the
Click to expand...
starting character in this case the 4.
That tells you the string 4600 is in the target cell,
Click to expand...
what you do with it - you have not indicated.
 
Pinda

Would Conditional Formatting do what you want?

Format> Conditional Formatting, and then fill in the boxes. You probably
want to change the first option to "Formula Is" and then enter somehting
like "=SEARCH(4600,A1)>0"

HTH
Mike

thanks for that.

As you can see from the data, each row of data is
identifier for who has been printing to what printer. I
want to flag 4600 because they are colour so I can
determine whats being printed. Can you think of the
easiest way that i could flag up this data?

Many thanks
-----Original Message-----
Good morning Pinda-

You may find SEARCH helpful.
An example:

=search("4600",<cellref>)
This will return a number that is the position of the
Click to expand...
starting character in this case the 4.
That tells you the string 4600 is in the target cell,
Click to expand...
what you do with it - you have not indicated.
 
Good afternoon Pinda-

To "flag" a cell you need to be able to identify it specifically.
There are 2 options that I would like to offer.

1. Insert a new column - use the formula to identify the cells that contain the 4600 value. =IF(ISERROR(SEARCH("4600",D5)),"",SEARCH("4600",D5))
You can then set conditioanl formatting so that if the cells value is greater than one make the interior color - red - for example.
2. You can evaluate and mark the cells witrh a macro.
Create a macro in the VBA editor:

Sub v4600()
On Error GoTo ender
y = ActiveCell.Find("4600")
ActiveCell.Interior.ColorIndex = 3
ender:
End Sub

If you select a cell and run this macro - if the value 4600 is in the cell it will set the interior color to Red, otherwise it will leave it alone.

By putting it in a looping routine it could evaluate a whole column of values by running it once.

Sub v4600_2()
For mcell = 1 To ActiveCell.End(xlDown).Row
On Error Resume Next
If y = ActiveCell.Find("4600") Then
ActiveCell.Interior.ColorIndex = xlNone
Else: ActiveCell.Interior.ColorIndex = 3
End If
ActiveCell.Offset(1, 0).Select
Next mcell
End Sub

Select the top cell in your CURRENT sheet - do not add the new column.
Run the macro - all cells that contain 4600 will be set to RED as it's Interior color

Hope that helps.
Jon Barchenger

--------------------
**Content-Class: urn:content-classes:message
**From: "Pinda" <[email protected]>
**Sender: "Pinda" <[email protected]>
**References: <[email protected]> <[email protected]>
**Subject: RE: Finding 4600
**Date: Tue, 18 Nov 2003 09:14:19 -0800
**Lines: 71
**Message-ID: <[email protected]>
**MIME-Version: 1.0
**Content-Type: text/plain;
** charset="iso-8859-1"
**Content-Transfer-Encoding: quoted-printable
**X-Newsreader: Microsoft CDO for Windows 2000
**Thread-Index: AcOt92dhMSHCkDFUS62HwrYbJaK0mQ==
**X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4910.0300
**Newsgroups: microsoft.public.excel.worksheet.functions
**Path: cpmsftngxa06.phx.gbl
**Xref: cpmsftngxa06.phx.gbl microsoft.public.excel.worksheet.functions:172058
**NNTP-Posting-Host: TK2MSFTNGXA11 10.40.1.163
**X-Tomcat-NG: microsoft.public.excel.worksheet.functions
**
**thanks for that.
**As you can see from the data, each row of data is
**identifier for who has been printing to what printer. I
**want to flag 4600 because they are colour so I can
**determine whats being printed. Can you think of the
**easiest way that i could flag up this data?
**Many thanks
**>-----Original Message-----
**>Good morning Pinda-
**>
**>You may find SEARCH helpful.
**>An example:
**>
**>=search("4600",<cellref>)
**>This will return a number that is the position of the
**starting character in this case the 4.
**>
**>That tells you the string 4600 is in the target cell,
**what you do with it - you have not indicated.
**>
**>Search can be imbeded in other formulas like most other
**functions.
**>
**>Let me know if you need further help or information
**concerning this issue.
**>
**>Thanks,
**>Jon Barchenger
**>--------------------
**>**Content-Class: urn:content-classes:message
**>**From: "Pinda" <[email protected]>
**>**Sender: "Pinda" <[email protected]>
**>**Subject: Finding 4600
**>**Date: Tue, 18 Nov 2003 01:31:15 -0800
**>**Lines: 13
**>**Message-ID: <[email protected]>
**>**MIME-Version: 1.0
**>**Content-Type: text/plain;
**>** charset="iso-8859-1"
**>**Content-Transfer-Encoding: quoted-printable
**>**X-Newsreader: Microsoft CDO for Windows 2000
**>**X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4910.0300
**>**Thread-Index: AcOttrZwGannM3QIQ5icxdyf1oFleA==
**>**Newsgroups: microsoft.public.excel.worksheet.functions
**>**Path: cpmsftngxa06.phx.gbl
**>**Xref: cpmsftngxa06.phx.gbl
**microsoft.public.excel.worksheet.functions:171984
**>**NNTP-Posting-Host: TK2MSFTNGXA08 10.40.1.160
**>**X-Tomcat-NG: microsoft.public.excel.worksheet.functions
**>**
**>**@vZ(PrintSMPCT-FS01úO /ýCFå;+|111Priority
**>**Practice Analysis.xlsSarahTHP Color 4600
**>**FalklandIP_62.130.88.322297110dLfLe¹f–?f–?
**>**I have rows and rows of data similar to this in a
**>**spreadsheet. the above data is within 1 cell. I need to
**>**create a lookup that returns all the rows with '4600'
**in
**>**the. The trouble is that the 4600 can be anywhere
**within
**>**the text string.
**>**Any ideas?
**>**Thanks in advanvce.
**>**
**>
**>
**>.
**>
**
 
Back
Top