Finding and getting text from a lot of cells in a row

[Kyusson]

Hi,

I am trying to find a text in a row, A9:AP9 where this value is: [FONT=Inconsolata, monospace, arial, sans, sans-serif]id: "bf00c6e0-b7a7-11e7-a8e6-0a580a2c491b"[/FONT]

[FONT=Inconsolata, monospace, arial, sans, sans-serif]I then want to take the value between the " (speech marks) and return it to another cell, so I would have [/FONT]bf00c6e0-b7a7-11e7-a8e6-0a580a2c491b

Is this possible? I have tried all sorts, but I cant seem to get ii to search the row. Any help appreciated.

 

[Special-K99]

The value is either found in that range or not found.

"Another cell" - cell unspecified, I'm assuming A1, change it to what you want.

in A1
=IFERROR(HLOOKUP("bf00c6e0-b7a7-11e7-a8e6-0a580a2c491b",A9:AP9,1,0),"NOT FOUND")

 

[Kyusson]

Ah interesting, thanks, I have tried HLOOKUP (I even tried ordering the data in a column and use VLOOKUP, no joy). Problem is, the only static text in the cell I am looking for is ID: The value that is inside the "" changes. So I cannot search for the specific string, just the start of the cell ID:

So, in a row of cells, I have a lot of data, in one of them is some text that starts ID:" then there will be the variable string value, then it closes with another "

So I want to retrieve the variable string value, which is inside those speech marks and preceded by ID:

So a value in a cell could be
id: "bf00c6e0-b7a7-11e7-a8e6-0a580a2c491b"
or
id: "dd00x5s0-h8a8-31w6-a9w7-0d568a2c921c"

Does that make sense and is it possible? Seems far fetched maybe.

 

[Rick Rothstein]

Ah interesting, thanks, I have tried HLOOKUP (I even tried ordering the data in a column and use VLOOKUP, no joy). Problem is, the only static text in the cell I am looking for is ID: The value that is inside the "" changes. So I cannot search for the specific string, just the start of the cell ID:

So, in a row of cells, I have a lot of data, in one of them is some text that starts ID:" then there will be the variable string value, then it closes with another "

So I want to retrieve the variable string value, which is inside those speech marks and preceded by ID:

So a value in a cell could be
id: "bf00c6e0-b7a7-11e7-a8e6-0a580a2c491b"
or
id: "dd00x5s0-h8a8-31w6-a9w7-0d568a2c921c"

Does that make sense and is it possible? Seems far fetched maybe.

For the text you are searching for...

1) Does it always start with "id:" whether upper or lower case?

2) Can there be more than one such cell in the range A9:AP9?

3) If there can be more, how do you want them presented back to you? In separate cells (if so which cells)? As a comma delimited list within a single cell? Something else?

 

[DanteAmor]

Ah interesting, thanks, I have tried HLOOKUP (I even tried ordering the data in a column and use VLOOKUP, no joy). Problem is, the only static text in the cell I am looking for is ID: The value that is inside the "" changes. So I cannot search for the specific string, just the start of the cell ID:

So, in a row of cells, I have a lot of data, in one of them is some text that starts ID:" then there will be the variable string value, then it closes with another "

So I want to retrieve the variable string value, which is inside those speech marks and preceded by ID:

So a value in a cell could be
id: "bf00c6e0-b7a7-11e7-a8e6-0a580a2c491b"
or
id: "dd00x5s0-h8a8-31w6-a9w7-0d568a2c921c"

Does that make sense and is it possible? Seems far fetched maybe.

Following your example, maybe:

<table border="1" cellspacing="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:33.27px;" /><col style="width:33.27px;" /><col style="width:33.27px;" /><col style="width:274.69px;" /><col style="width:33.27px;" /><col style="width:33.27px;" /><col style="width:33.27px;" /><col style="width:33.27px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td><td >D</td><td >E</td><td >F</td><td >G</td><td >H</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td > </td><td > </td><td > </td><td >bf00c6e0-b7a7-11e7-a8e6-0a580a2c491b</td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >8</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >9</td><td >Data</td><td >Data</td><td >Data</td><td >ID: "bf00c6e0-b7a7-11e7-a8e6-0a580a2c491b"</td><td >Data</td><td >Data</td><td >Data</td><td >Data</td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b></b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Formula</td></tr><tr><td >D6</td><td >=TRIM(SUBSTITUTE(MID(INDEX(A9:AP9,0,MATCH("id:*",A9:AP9,0)),6,250),CHAR(34),""))</td></tr></table></td></tr></table>

Try and tell me.

 

[Kyusson]

Following your example, maybe:

	A	B	C	D	E	F	G	H
			bf00c6e0-b7a7-11e7-a8e6-0a580a2c491b
Data	Data	Data	ID: "bf00c6e0-b7a7-11e7-a8e6-0a580a2c491b"	Data	Data	Data	Data

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:33.27px;"><col style="width:33.27px;"><col style="width:33.27px;"><col style="width:274.69px;"><col style="width:33.27px;"><col style="width:33.27px;"><col style="width:33.27px;"><col style="width:33.27px;"></colgroup><tbody>
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]6[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]7[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]8[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]9[/TD]

</tbody>

Cell Formula D6 =TRIM(SUBSTITUTE(MID(INDEX(A9:AP9,0,MATCH("id:*",A9:AP9,0)),6,250),CHAR(34),"")) <tbody> </tbody>

<tbody>
</tbody>

Try and tell me.

YES!!

This entirely works

 

[DanteAmor]

YES!!

This entirely works

I'm glad to help you. Thanks for the feedback.