Excel: Search for text and return whole cell

January 17, 2011 at 07:39:27
Specs: Windows XP
I am trying to re arrange a list of addresses in a worksheet. This involves searching for different kinds of information and extracting them from the one column (eg addresses, phone numbers etc)

The telephone number occurs in a slightly different place every time and always occurs in this format: "tel: 01234567",

I'm thinking that I need a formula to search within a small range for text that begins with "tel:" and then to take the entire contents of that cell and display them in another part of the worksheet.

I have tried to use 'if' and 'Find' formulas but have had problems because I'm searching for only part of the text within a cell.

Any help would be much appreciated!


See More: Excel: Search for text and return whole cell

Report •

#1
January 17, 2011 at 10:16:43
Is this what you are looking for?

It will return the value in the cell if tel: exists anywhere in the cell.

=IF(ISERROR(SEARCH("tel:",A1)),"",A1)

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#2
January 18, 2011 at 04:00:13
Thanks DerbyDad - this is along the lines of what I need, but I need to search a range of three cells to find the one entry that contains the text 'tel' and display it. As in I need to look not just within cell A1, but cells A1 to A3 for eg. The consecutive cells are always in a column and only one cell will contain 'tel'.

Report •

#3
January 18, 2011 at 05:31:28
Since you stated that only one cell will contain, tel:, this formula will check A1 and return A1 if it contains tel: and then stop. If A1 doesn't contain tel:, it will check A2 and return A2 if it contains tel: and then stop. If neither A1 or A2 contain tel:, it will return A3 since that's the only cell left.

If you need to actually check A3 because there might not be a tel: in any cell, then just add one more IF clause.


=IF(NOT(ISERROR(SEARCH("tel:",A1))),A1,IF(NOT(ISERROR(SEARCH("tel:",A2))),A2,A3))

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •
Related Solutions


Ask Question