Extract Variable Text String

[Abraxas365]

Ok, I got a bunch of data rows with the following kind of data:

FACILITYID</td><td>H1676</td></
FACILITYID</td><td>PN1354</td>
FACILITYID</td><td>61523/54712</

... and so on.

I need to extract the unique data, no matter the length, from each cell. Unfortunately, a lot of characters like "<" are repeated, so I don't know how to tell Excel to stop at a certain "<" and give me an answer with a variable length that doesn't include any "<" or "/" (except in situations like the 3rd row, where I need the 10 digit number separated by "/".

All cells begin with "FACILITYID</td><td>" and then it's the number I want, and then it's junk for a couple of characters that I don't need.

Any help would be appreciated!

 

[jim may]

Welcome to the Board...

Show us the expected extracted data results for each of the 2 given rows. I guess your data is all in Column A. If not tell us more...

 

[jtakw]

Hi,

Based on your 3 samples and written description, try these, either B1 or C1 formula copied down:

Book1
	A	B	C
1	FACILITYID	H1676	H1676	H1676
2	FACILITYID	PN1354	PN1354	PN1354
3	FACILITYID	61523/54712</</td>	61523/54712	61523/54712
Sheet641

Cell Formulas
Range	Formula
B1	=TRIM(LEFT(SUBSTITUTE(MID(A1,20,99),"<",REPT(" ",99)),99))
C1	=TRIM(MID(SUBSTITUTE(A1,"<",REPT(" ",99),3),20,99))

 

[Abraxas365]

Hi,

Based on your 3 samples and written description, try these, either B1 or C1 formula copied down:

	A	B	C
FACILITYID</td><td>H1676</td></	H1676	H1676
FACILITYID</td><td>PN1354</td>	PN1354	PN1354
FACILITYID</td><td>61523/54712</	61523/54712	61523/54712

<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]

[TD="align: center"]3[/TD]

</tbody>

Sheet641​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B1[/TH]
[TD="align: left"]=TRIM(LEFT(SUBSTITUTE(MID(A1,20,99),"<",REPT(" ",99)),99))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]C1[/TH]
[TD="align: left"]=TRIM(MID(SUBSTITUTE(A1,"<",REPT(" ",99),3),20,99))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

I realized after I posted that some of the cell information was being translated (like code) and didn't know how to edit it. I was working on a reply to clear it up but you guys knew what I wanted anyway.

When I try this, all my results begin with "d>" but I changed 20 to 22 and it works.

Thanks so much!

 

[jtakw]

You're welcome, you must have 2 additional leading characters that I couldn't account for, but glad it works for you.

Welcome to the forum.