CODEWITHSUNDEEP
excelexcel-formulaexcel-2013vba
J Cos
J Cos

Reputation: 23

excel search range for specific text copy cell containing text

I need some assistance with an excel spreadsheet. I'm not very proficient with excel and not familiar with excel-vba but I'm open to suggestions and willing to learn to get this to work properly.

I need to have a formula search a range of columns and pull only the one containing the required information into a new column but keep the original row #.

Example of spreadsheet
  enter image description here

I found the below which sort of does what I'm looking for but it's only for a single column. How can I make it search a range of cells & copy only the one containing the identifying character ie (h), (c), or (w) to pull it to the appropriate column.

Copy a cell if another cell contains a specific text string

How do I use a formula to search the range of cells containing the phone numbers then pull the one cell containing (h) under home and so on for the others. I need the number to remain in the same row so it stays assigned to the correct contact when uploaded as well.

I'm trying to organize phone numbers into select columns from a range of cells.

I have a list of phone numbers that when pulled from the database are drop into a single cell, I use text to column to spread it so each number is in its own cells. From here I need to organize it so each number is copied under the appropriate field Home, Work, Cell.

Upvotes: 2

Views: 2975

Answers (2)

user4039065
user4039065

Reputation:

An INDEX/MATCH function pair should do this handily with a wildcard match on the phone type designation.

In X14:Z14 as standard formulas,

=INDEX($R14:$T14, MATCH("*(h)", $R14:$T14, 0))
=INDEX($R14:$T14, MATCH("*(w)", $R14:$T14, 0))
=INDEX($R14:$T14, MATCH("*(c)", $R14:$T14, 0))

Fill down as necessary.

  home_work_cell

Upvotes: 1

Scott Craner
Scott Craner

Reputation: 152450

Use the following Array formulas:

X14:

=INDEX($R14:$T14,MATCH(1, IF(ISNUMBER(SEARCH("h",$R14:$T14)),1,0),0))

Y14:

=INDEX($R14:$T14,MATCH(1, IF(ISNUMBER(SEARCH("w",$R14:$T14)),1,0),0))

Z14:

=INDEX($R14:$T14,MATCH(1, IF(ISNUMBER(SEARCH("c",$R14:$T14)),1,0),0))

These are array formulas and must be confirmed with Ctrl-Shift-Enter when leaving edit mode instead of Enter.

Then copy all three down.

enter image description here

Upvotes: 1

Related Questions