Find text in Column A that appears in Text in Column B

R

robertguy

Board Regular
Joined
May 1, 2008
Messages
121
Hi,

In column 'A' I have text in cells A2:A1000 and want to check if any of this text appears any cell in Column 'B' - one big problem is, whilst the text in column 'A' can be a maximum of 20 characters/numbers in column 'B' the text to check for could me amonst text/numbers upto 256 characters.

If the text in column 'A' is found in any cell Column 'B' Column 'C' should be updated with the cell ref in Column 'B'

e.g

Row Column A Column B Column C
1 1000 the cat sat on the mat 9999 A2
2 2000 the 1000 cat sat on the mat A4
3 9999 the dog ate the 6666 cat A1
4 6666 the dog sat on the 2000 cat A3


Any assistance would be gratefully appreciated

Many thanks in advance

Rob
 

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.
Sort by date Sort by votes
robertguy said:
Hi,

In column 'A' I have text in cells A2:A1000 and want to check if any of this text appears any cell in Column 'B' - one big problem is, whilst the text in column 'A' can be a maximum of 20 characters/numbers in column 'B' the text to check for could me amonst text/numbers upto 256 characters.

If the text in column 'A' is found in any cell Column 'B' Column 'C' should be updated with the cell ref in Column 'B'

e.g

Row Column A Column B Column C
1 1000 the cat sat on the mat 9999 A2
2 2000 the 1000 cat sat on the mat A4
3 9999 the dog ate the 6666 cat A1
4 6666 the dog sat on the 2000 cat A3


Any assistance would be gratefully appreciated

Many thanks in advance

Rob
Click to expand...
C2, copy down:

=INDEX($B$2:$B$100,MATCH("*"&A2&"*",$B$2:$B$100,0))

If on 2007 or later:

=IFERROR(INDEX($B$2:$B$100,MATCH("*"&A2&"*",$B$2:$B$100,0)),"")
 
Upvote 0
robertguy said:
Hi,

In column 'A' I have text in cells A2:A1000 and want to check if any of this text appears any cell in Column 'B' - one big problem is, whilst the text in column 'A' can be a maximum of 20 characters/numbers in column 'B' the text to check for could me amonst text/numbers upto 256 characters.

If the text in column 'A' is found in any cell Column 'B' Column 'C' should be updated with the cell ref in Column 'B'

e.g

Row Column A Column B Column C
1 1000 the cat sat on the mat 9999 A2
2 2000 the 1000 cat sat on the mat A4
3 9999 the dog ate the 6666 cat A1
4 6666 the dog sat on the 2000 cat A3


Any assistance would be gratefully appreciated

Many thanks in advance

Rob
Click to expand...
Maybe this...

Book1
ABC
11000the cat sat on the mat 9999A2
22000the 1000 cat sat on the matA4
39999the dog ate the 6666 catA1
46666the dog sat on the 2000 catA3
Sheet1

This formula entered in C1 and copied down:

=ADDRESS(MATCH("*"&A1&"*",B:B,0),1,4)
 
Upvote 0
Many thanks guys for your prompt responses it is very much appreciated, just what I wanted.


Regards


Rob
 
Upvote 0
T Valko,

is it possible to amend your formula =ADDRESS(MATCH("*"&A1&"*",B:B,0),1,4) to also check if the text is duplicated in Column 'B'

i.e. If the text in column 'A' is found in any cell or cells in Column 'B' Column 'C' should be updated with the cell refrences from Column 'B'

I know its a big ask, but it would be of great help if you could crack this one for me.

Many thankd in advance

Rob

N.B. Excel version 2003
 
Upvote 0
robertguy said:
T Valko,

is it possible to amend your formula =ADDRESS(MATCH("*"&A1&"*",B:B,0),1,4) to also check if the text is duplicated in Column 'B'

i.e. If the text in column 'A' is found in any cell or cells in Column 'B' Column 'C' should be updated with the cell refrences from Column 'B'

I know its a big ask, but it would be of great help if you could crack this one for me.

Many thankd in advance

Rob

N.B. Excel version 2003
Click to expand...
Can you post some sample data to demonstrate that along with the expected result?
 
Upvote 0
T. Valko here is some example data colour coded with the expected results in Column 'C'

<TABLE style="WIDTH: 321pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=428 x:str><COLGROUP><COL style="WIDTH: 48pt" span=2 width=64><COL style="WIDTH: 98pt; mso-width-source: userset; mso-width-alt: 4790" width=131><COL style="WIDTH: 127pt; mso-width-source: userset; mso-width-alt: 6180" width=169><TBODY><TR style="HEIGHT: 13.5pt" height=18><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #666699 1pt solid; BACKGROUND-COLOR: silver; WIDTH: 48pt; HEIGHT: 13.5pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl22 height=18 width=64></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: silver; WIDTH: 48pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl23 width=64>A</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: silver; WIDTH: 98pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl23 width=131>B</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: silver; WIDTH: 127pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #666699 1pt solid" class=xl24 width=169>C</TD></TR><TR style="HEIGHT: 26.25pt" height=35><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #666699 1pt solid; BACKGROUND-COLOR: silver; WIDTH: 48pt; HEIGHT: 26.25pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl25 height=35 width=64 x:num>1</TD><TD style="BORDER-BOTTOM: #666699 1pt solid; BORDER-LEFT: #666699 1pt solid; BACKGROUND-COLOR: white; WIDTH: 48pt; BORDER-TOP: #666699 1pt solid; BORDER-RIGHT: #666699 1pt solid" class=xl26 width=64 x:num>1000</TD><TD style="BORDER-BOTTOM: #666699 1pt solid; BORDER-LEFT: #666699; BACKGROUND-COLOR: white; WIDTH: 98pt; BORDER-TOP: #666699 1pt solid; BORDER-RIGHT: #666699 1pt solid" class=xl27 width=131>the cat 2468 sat on the mat 9999</TD><TD style="BORDER-BOTTOM: #666699 1pt solid; BORDER-LEFT: #666699; BACKGROUND-COLOR: white; WIDTH: 127pt; BORDER-TOP: #666699 1pt solid; BORDER-RIGHT: #666699 1pt solid" class=xl26 width=169>B2, B6</TD></TR><TR style="HEIGHT: 26.25pt" height=35><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #666699 1pt solid; BACKGROUND-COLOR: silver; WIDTH: 48pt; HEIGHT: 26.25pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl25 height=35 width=64 x:num>2</TD><TD style="BORDER-BOTTOM: #666699 1pt solid; BORDER-LEFT: #666699 1pt solid; BACKGROUND-COLOR: white; WIDTH: 48pt; BORDER-TOP: #666699; BORDER-RIGHT: #666699 1pt solid" class=xl28 width=64 x:num>2000</TD><TD style="BORDER-BOTTOM: #666699 1pt solid; BORDER-LEFT: #666699; BACKGROUND-COLOR: white; WIDTH: 98pt; BORDER-TOP: #666699; BORDER-RIGHT: #666699 1pt solid" class=xl27 width=131>the 1000 cat sat 2468 on the mat</TD><TD style="BORDER-BOTTOM: #666699 1pt solid; BORDER-LEFT: #666699; BACKGROUND-COLOR: white; WIDTH: 127pt; BORDER-TOP: #666699; BORDER-RIGHT: #666699 1pt solid" class=xl28 width=169>B3, B4</TD></TR><TR style="HEIGHT: 26.25pt" height=35><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #666699 1pt solid; BACKGROUND-COLOR: silver; WIDTH: 48pt; HEIGHT: 26.25pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl25 height=35 width=64 x:num>3</TD><TD style="BORDER-BOTTOM: #666699 1pt solid; BORDER-LEFT: #666699 1pt solid; BACKGROUND-COLOR: white; WIDTH: 48pt; BORDER-TOP: #666699; BORDER-RIGHT: #666699 1pt solid" class=xl29 width=64 x:num>9999</TD><TD style="BORDER-BOTTOM: #666699 1pt solid; BORDER-LEFT: #666699; BACKGROUND-COLOR: white; WIDTH: 98pt; BORDER-TOP: #666699; BORDER-RIGHT: #666699 1pt solid" class=xl27 width=131>the dog 2000 ate the 6666 cat</TD><TD style="BORDER-BOTTOM: #666699 1pt solid; BORDER-LEFT: #666699; BACKGROUND-COLOR: white; WIDTH: 127pt; BORDER-TOP: #666699; BORDER-RIGHT: #666699 1pt solid" class=xl29 width=169>B1</TD></TR><TR style="HEIGHT: 26.25pt" height=35><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #666699 1pt solid; BACKGROUND-COLOR: silver; WIDTH: 48pt; HEIGHT: 26.25pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl25 height=35 width=64 x:num>4</TD><TD style="BORDER-BOTTOM: #666699 1pt solid; BORDER-LEFT: #666699 1pt solid; BACKGROUND-COLOR: white; WIDTH: 48pt; BORDER-TOP: #666699; BORDER-RIGHT: #666699 1pt solid" class=xl30 width=64 x:num>6666</TD><TD style="BORDER-BOTTOM: #666699 1pt solid; BORDER-LEFT: #666699; BACKGROUND-COLOR: white; WIDTH: 98pt; BORDER-TOP: #666699; BORDER-RIGHT: #666699 1pt solid" class=xl27 width=131>the dog sat on the 2000 cat</TD><TD style="BORDER-BOTTOM: #666699 1pt solid; BORDER-LEFT: #666699; BACKGROUND-COLOR: white; WIDTH: 127pt; BORDER-TOP: #666699; BORDER-RIGHT: #666699 1pt solid" class=xl31 width=169>B3</TD></TR><TR style="HEIGHT: 26.25pt" height=35><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #666699 1pt solid; BACKGROUND-COLOR: silver; WIDTH: 48pt; HEIGHT: 26.25pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl25 height=35 width=64 x:num>5</TD><TD style="BORDER-BOTTOM: #666699 1pt solid; BORDER-LEFT: #666699 1pt solid; BACKGROUND-COLOR: white; WIDTH: 48pt; BORDER-TOP: #666699; BORDER-RIGHT: #666699 1pt solid" class=xl32 width=64 x:num>2468</TD><TD style="BORDER-BOTTOM: #666699 1pt solid; BORDER-LEFT: #666699; BACKGROUND-COLOR: white; WIDTH: 98pt; BORDER-TOP: #666699; BORDER-RIGHT: #666699 1pt solid" class=xl33 width=131>3333 is the code 2000 OK</TD><TD style="BORDER-BOTTOM: #666699 1pt solid; BORDER-LEFT: #666699; BACKGROUND-COLOR: white; WIDTH: 127pt; BORDER-TOP: #666699; BORDER-RIGHT: #666699 1pt solid" class=xl34 width=169>B2</TD></TR><TR style="HEIGHT: 26.25pt" height=35><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #666699 1pt solid; BACKGROUND-COLOR: silver; WIDTH: 48pt; HEIGHT: 26.25pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl25 height=35 width=64 x:num>6</TD><TD style="BORDER-BOTTOM: #666699 1pt solid; BORDER-LEFT: #666699 1pt solid; BACKGROUND-COLOR: white; WIDTH: 48pt; BORDER-TOP: #666699; BORDER-RIGHT: #666699 1pt solid" class=xl33 width=64 x:num>3333</TD><TD style="BORDER-BOTTOM: #666699 1pt solid; BORDER-LEFT: #666699; BACKGROUND-COLOR: white; WIDTH: 98pt; BORDER-TOP: #666699; BORDER-RIGHT: #666699 1pt solid" class=xl27 width=131>hello 1000 and goodbye</TD><TD style="BORDER-BOTTOM: #666699 1pt solid; BORDER-LEFT: #666699; BACKGROUND-COLOR: white; WIDTH: 127pt; BORDER-TOP: #666699; BORDER-RIGHT: #666699 1pt solid" class=xl33 width=169>B5</TD></TR><TR style="HEIGHT: 26.25pt" height=35><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #666699 1pt solid; BACKGROUND-COLOR: silver; WIDTH: 48pt; HEIGHT: 26.25pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl25 height=35 width=64 x:num>7</TD><TD style="BORDER-BOTTOM: #666699 1pt solid; BORDER-LEFT: #666699 1pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: #666699; BORDER-RIGHT: #666699 1pt solid" class=xl35 x:num>3695</TD><TD style="BORDER-BOTTOM: #666699 1pt solid; BORDER-LEFT: #666699; BACKGROUND-COLOR: white; WIDTH: 98pt; BORDER-TOP: #666699; BORDER-RIGHT: #666699 1pt solid" class=xl27 width=131>Thanks very much T. Valko</TD><TD style="BORDER-BOTTOM: #666699 1pt solid; BORDER-LEFT: #666699; BACKGROUND-COLOR: transparent; BORDER-TOP: #666699; BORDER-RIGHT: #666699 1pt solid" class=xl35>Not found</TD></TR></TBODY></TABLE>

Many thanks

Rob
 
Upvote 0
robertguy said:
T. Valko here is some example data colour coded with the expected results in Column 'C'

<TABLE style="WIDTH: 321pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=428 border=0 x:str><COLGROUP><COL style="WIDTH: 48pt" span=2 width=64><COL style="WIDTH: 98pt; mso-width-source: userset; mso-width-alt: 4790" width=131><COL style="WIDTH: 127pt; mso-width-source: userset; mso-width-alt: 6180" width=169><TBODY><TR style="HEIGHT: 13.5pt" height=18><TD class=xl22 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #666699 1pt solid; WIDTH: 48pt; BORDER-BOTTOM: #f0f0f0; HEIGHT: 13.5pt; BACKGROUND-COLOR: silver" width=64 height=18></TD><TD class=xl23 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; WIDTH: 48pt; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: silver" width=64>A</TD><TD class=xl23 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; WIDTH: 98pt; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: silver" width=131>B</TD><TD class=xl24 style="BORDER-RIGHT: #666699 1pt solid; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; WIDTH: 127pt; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: silver" width=169>C</TD></TR><TR style="HEIGHT: 26.25pt" height=35><TD class=xl25 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #666699 1pt solid; WIDTH: 48pt; BORDER-BOTTOM: #f0f0f0; HEIGHT: 26.25pt; BACKGROUND-COLOR: silver" width=64 height=35 x:num>1</TD><TD class=xl26 style="BORDER-RIGHT: #666699 1pt solid; BORDER-TOP: #666699 1pt solid; BORDER-LEFT: #666699 1pt solid; WIDTH: 48pt; BORDER-BOTTOM: #666699 1pt solid; BACKGROUND-COLOR: white" width=64 x:num>1000</TD><TD class=xl27 style="BORDER-RIGHT: #666699 1pt solid; BORDER-TOP: #666699 1pt solid; BORDER-LEFT: #666699; WIDTH: 98pt; BORDER-BOTTOM: #666699 1pt solid; BACKGROUND-COLOR: white" width=131>the cat 2468 sat on the mat 9999</TD><TD class=xl26 style="BORDER-RIGHT: #666699 1pt solid; BORDER-TOP: #666699 1pt solid; BORDER-LEFT: #666699; WIDTH: 127pt; BORDER-BOTTOM: #666699 1pt solid; BACKGROUND-COLOR: white" width=169>B2, B6</TD></TR><TR style="HEIGHT: 26.25pt" height=35><TD class=xl25 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #666699 1pt solid; WIDTH: 48pt; BORDER-BOTTOM: #f0f0f0; HEIGHT: 26.25pt; BACKGROUND-COLOR: silver" width=64 height=35 x:num>2</TD><TD class=xl28 style="BORDER-RIGHT: #666699 1pt solid; BORDER-TOP: #666699; BORDER-LEFT: #666699 1pt solid; WIDTH: 48pt; BORDER-BOTTOM: #666699 1pt solid; BACKGROUND-COLOR: white" width=64 x:num>2000</TD><TD class=xl27 style="BORDER-RIGHT: #666699 1pt solid; BORDER-TOP: #666699; BORDER-LEFT: #666699; WIDTH: 98pt; BORDER-BOTTOM: #666699 1pt solid; BACKGROUND-COLOR: white" width=131>the 1000 cat sat 2468 on the mat</TD><TD class=xl28 style="BORDER-RIGHT: #666699 1pt solid; BORDER-TOP: #666699; BORDER-LEFT: #666699; WIDTH: 127pt; BORDER-BOTTOM: #666699 1pt solid; BACKGROUND-COLOR: white" width=169>B3, B4</TD></TR><TR style="HEIGHT: 26.25pt" height=35><TD class=xl25 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #666699 1pt solid; WIDTH: 48pt; BORDER-BOTTOM: #f0f0f0; HEIGHT: 26.25pt; BACKGROUND-COLOR: silver" width=64 height=35 x:num>3</TD><TD class=xl29 style="BORDER-RIGHT: #666699 1pt solid; BORDER-TOP: #666699; BORDER-LEFT: #666699 1pt solid; WIDTH: 48pt; BORDER-BOTTOM: #666699 1pt solid; BACKGROUND-COLOR: white" width=64 x:num>9999</TD><TD class=xl27 style="BORDER-RIGHT: #666699 1pt solid; BORDER-TOP: #666699; BORDER-LEFT: #666699; WIDTH: 98pt; BORDER-BOTTOM: #666699 1pt solid; BACKGROUND-COLOR: white" width=131>the dog 2000 ate the 6666 cat</TD><TD class=xl29 style="BORDER-RIGHT: #666699 1pt solid; BORDER-TOP: #666699; BORDER-LEFT: #666699; WIDTH: 127pt; BORDER-BOTTOM: #666699 1pt solid; BACKGROUND-COLOR: white" width=169>B1</TD></TR><TR style="HEIGHT: 26.25pt" height=35><TD class=xl25 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #666699 1pt solid; WIDTH: 48pt; BORDER-BOTTOM: #f0f0f0; HEIGHT: 26.25pt; BACKGROUND-COLOR: silver" width=64 height=35 x:num>4</TD><TD class=xl30 style="BORDER-RIGHT: #666699 1pt solid; BORDER-TOP: #666699; BORDER-LEFT: #666699 1pt solid; WIDTH: 48pt; BORDER-BOTTOM: #666699 1pt solid; BACKGROUND-COLOR: white" width=64 x:num>6666</TD><TD class=xl27 style="BORDER-RIGHT: #666699 1pt solid; BORDER-TOP: #666699; BORDER-LEFT: #666699; WIDTH: 98pt; BORDER-BOTTOM: #666699 1pt solid; BACKGROUND-COLOR: white" width=131>the dog sat on the 2000 cat</TD><TD class=xl31 style="BORDER-RIGHT: #666699 1pt solid; BORDER-TOP: #666699; BORDER-LEFT: #666699; WIDTH: 127pt; BORDER-BOTTOM: #666699 1pt solid; BACKGROUND-COLOR: white" width=169>B3</TD></TR><TR style="HEIGHT: 26.25pt" height=35><TD class=xl25 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #666699 1pt solid; WIDTH: 48pt; BORDER-BOTTOM: #f0f0f0; HEIGHT: 26.25pt; BACKGROUND-COLOR: silver" width=64 height=35 x:num>5</TD><TD class=xl32 style="BORDER-RIGHT: #666699 1pt solid; BORDER-TOP: #666699; BORDER-LEFT: #666699 1pt solid; WIDTH: 48pt; BORDER-BOTTOM: #666699 1pt solid; BACKGROUND-COLOR: white" width=64 x:num>2468</TD><TD class=xl33 style="BORDER-RIGHT: #666699 1pt solid; BORDER-TOP: #666699; BORDER-LEFT: #666699; WIDTH: 98pt; BORDER-BOTTOM: #666699 1pt solid; BACKGROUND-COLOR: white" width=131>3333 is the code 2000 OK</TD><TD class=xl34 style="BORDER-RIGHT: #666699 1pt solid; BORDER-TOP: #666699; BORDER-LEFT: #666699; WIDTH: 127pt; BORDER-BOTTOM: #666699 1pt solid; BACKGROUND-COLOR: white" width=169>B2</TD></TR><TR style="HEIGHT: 26.25pt" height=35><TD class=xl25 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #666699 1pt solid; WIDTH: 48pt; BORDER-BOTTOM: #f0f0f0; HEIGHT: 26.25pt; BACKGROUND-COLOR: silver" width=64 height=35 x:num>6</TD><TD class=xl33 style="BORDER-RIGHT: #666699 1pt solid; BORDER-TOP: #666699; BORDER-LEFT: #666699 1pt solid; WIDTH: 48pt; BORDER-BOTTOM: #666699 1pt solid; BACKGROUND-COLOR: white" width=64 x:num>3333</TD><TD class=xl27 style="BORDER-RIGHT: #666699 1pt solid; BORDER-TOP: #666699; BORDER-LEFT: #666699; WIDTH: 98pt; BORDER-BOTTOM: #666699 1pt solid; BACKGROUND-COLOR: white" width=131>hello 1000 and goodbye</TD><TD class=xl33 style="BORDER-RIGHT: #666699 1pt solid; BORDER-TOP: #666699; BORDER-LEFT: #666699; WIDTH: 127pt; BORDER-BOTTOM: #666699 1pt solid; BACKGROUND-COLOR: white" width=169>B5</TD></TR><TR style="HEIGHT: 26.25pt" height=35><TD class=xl25 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #666699 1pt solid; WIDTH: 48pt; BORDER-BOTTOM: #f0f0f0; HEIGHT: 26.25pt; BACKGROUND-COLOR: silver" width=64 height=35 x:num>7</TD><TD class=xl35 style="BORDER-RIGHT: #666699 1pt solid; BORDER-TOP: #666699; BORDER-LEFT: #666699 1pt solid; BORDER-BOTTOM: #666699 1pt solid; BACKGROUND-COLOR: transparent" x:num>3695</TD><TD class=xl27 style="BORDER-RIGHT: #666699 1pt solid; BORDER-TOP: #666699; BORDER-LEFT: #666699; WIDTH: 98pt; BORDER-BOTTOM: #666699 1pt solid; BACKGROUND-COLOR: white" width=131>Thanks very much T. Valko</TD><TD class=xl35 style="BORDER-RIGHT: #666699 1pt solid; BORDER-TOP: #666699; BORDER-LEFT: #666699; BORDER-BOTTOM: #666699 1pt solid; BACKGROUND-COLOR: transparent">Not found</TD></TR></TBODY></TABLE>

Many thanks

Rob
Click to expand...
You will need to use a custom programmed function to do this.

There is an add-in that has a range concatenation function that might be able to do this but the add-in is kind of "buggy" in Excel versions 2007 or later so I'm hesitant to recommend using it.

I'm not much of a programmer so someone else will need to help you out with this.
 
Upvote 0
T. Valko said:
You will need to use a custom programmed function to do this.

There is an add-in that has a range concatenation function that might be able to do this but the add-in is kind of "buggy" in Excel versions 2007 or later so I'm hesitant to recommend using it.

I'm not much of a programmer so someone else will need to help you out with this.
Click to expand...
If you can return each address to its own cell like this:

Book1
ABCD
11000the cat 2468 sat on the mat 9999B2B6
22000the 1000 cat sat 2468 on the matB3B4
39999the dog 2000 ate the 6666 catB1
46666the dog sat on the 2000 catB3
524683333 is the code 2000 OKB2
63333hello 1000 and goodbyeB5
73695Thanks very much T. ValkoNot found
Sheet1

Then we can use built-in functions to do it that way.
 
Upvote 0
You must log in or register to reply here.

Similar threads

Obewan
  • Question Question
Find text and then show text
2
Replies
13
Views
680
Peter_SSs
Peter_SSs
C
  • Solved
Multi Line Cell - Find Text String
Replies
3
Views
1K
Fluff
Fluff
Y
  • Question Question
Compare first 3 characters in two columns to find multiple matchings
Replies
3
Views
1K
DanteAmor
DanteAmor
Obewan
  • Locked
  • Question Question
Formula to write name when seen in text
Replies
2
Views
345
Fluff
Fluff
A
  • Solved
Bank statement conciliation: find a string match and link the desired sub-category
2
Replies
12
Views
1K
aduroche
A

Forum statistics

Threads
1,224,566
Messages
6,179,555
Members
452,928
Latest member
101blockchains

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top