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
1	FACILITYID</td><td>H1676</td></	H1676	H1676
2	FACILITYID</td><td>PN1354</td>	PN1354	PN1354
3	FACILITYID</td><td>61523/54712</	61523/54712	61523/54712

<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet641​

Worksheet Formulas Cell Formula B1 =TRIM(LEFT(SUBSTITUTE(MID(A1,20,99),"<",REPT(" ",99)),99)) C1 =TRIM(MID(SUBSTITUTE(A1,"<",REPT(" ",99),3),20,99)) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

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.