Extract from Cell with less helper columns...

[How_Do_I]

Hello are there any alternatives to my helper, helper, helper columns please...!

Excel Workbook
	A	B	C	D	E	F	G
1	x;;;;;;;;xx;SITE NAME: XI/A/58;xxxxxxxxxxx	13	31	18	11	7	XI/A/58
2	x;SITE NAME: XI/A/15A;xxxxxxxxxxx	3	22	19	11	8	XI/A/15A
3	xxxxxxxx;SITE NAME: XI/A/5;xxxxxxxxxxx	10	27	17	11	6	XI/A/5
4	xxxx;SITE NAME: XI/A/115A;xxxxxxxxxxx	6	26	20	11	9	XI/A/115A
5	xxxx;SITE NAME: XI/A/115;xxxxxxxxxxx	6	25	19	11	8	XI/A/115
Sheet1

 

[How_Do_I]

Sorry,

=IFERROR(MID(A1,FIND("SITE NAME:",A1)+11,F1),"")

is my latest G1...

 

[AliGW]

Just combine them all into one (no helper columns required):

=IFERROR(MID(A1,FIND("SITE NAME:",A1)+11,FIND(";",A1,FIND("SITE NAME:",A1))-FIND("SITE NAME:",A1)-11),"")

 

[How_Do_I]

Thanks AliGW... I had just done that and thought "What a mess..." but it gives me what is required without multiple helper columns...

 

[AliGW]

Well, you can't make an omelette without breaking eggs ...

There may be a smarter way, but it does what you want, so I wouldn't worry too much.

 

[ukmikeb]

An alternative :-

=TRIM(LEFT(SUBSTITUTE(TRIM(RIGHT(SUBSTITUTE(A31,"SITE NAME:",REPT(" ",LEN(A31))),LEN(A31))),";",REPT(" ",LEN(A31))),LEN(A31)))

without relying on the length of a constituent part.

hth

 

[How_Do_I]

Thanks for your solution Mike... I see that normal service has been resumed at the Bridge and indeed at other venues, I'm declaring the Blues as serious title contenders... Jose who...?

 

[Rick Rothstein]

If there are never any semi-colons among all those X's, and assuming your overall text is never longer than 300 characters, then here is another alternative you can consider...

=MID(TRIM(LEFT(RIGHT(SUBSTITUTE(A1,";",REPT(" ",300)),600),300)),12,99)

 

[How_Do_I]

Thanks Rick, it is downloaded data from the Internet and there are semi-colons before the one I'm interested in and some of the cells do contain more than 300 characters... That said, the data I want is always between "SITE NAME:" and the second semi-colon...

 

[Rick Rothstein]

Thanks Rick, it is downloaded data from the Internet and there are semi-colons before the one I'm interested in and some of the cells do contain more than 300 characters... That said, the data I want is always between "SITE NAME:" and the second semi-colon...

The second semi-colon? From the left side? That is not what you showed us in your examples in your original message. Please verify... is it always the second semi-colon from the left?

What about the my assumption.. I know there is always one, but will there ever be two semi-colons after the text you want to retrieve? In other words, will there ever be a semi-colon with the text you show as "xxx.."?

Can you give us an idea about how much bigger than 300 characters there can be in a cell?