Extract after symbols

[Celticshadow]

Hi

How do extract the data using an excel formula from after each = symbol in the below.

In Cell F1 I have,

hcap=Y class=1 racetype=FLT racetype2=Listed

and in cells G1 through to j1 i require the below data

G1 - Y
H1 - 1
I1 - FLT
J1 - Listed

Many thanks in advance.

Regards

 

[Pup Denab]

=IF(FIND($G$1,F2,1)>0,$G$1,"")

=IF(FIND($H$1,F2,1)>0,$H$1,"")

=IF(FIND($I$1,F2,1)>0,$I$1,"")

=IF(FIND($J$1,F2,1)>0,$J$1,"")

<tbody>
</tbody>

Hi Celticshadow
This should do it, just place in cells G2 - J2

 

[Celticshadow]

=IF(FIND($G$1,F2,1)>0,$G$1,"")

=IF(FIND($H$1,F2,1)>0,$H$1,"")

=IF(FIND($I$1,F2,1)>0,$I$1,"")

=IF(FIND($J$1,F2,1)>0,$J$1,"")

<tbody>
</tbody>

Hi Celticshadow
This should do it, just place in cells G2 - J2

Hi Pup Denab

Many thanks for replying. Unfortunately when I enter the above formulas into the cells they all return a 0.

Kind Regards

 

[Celticshadow]

Hi Everyone

Perhaps a table may make the problem and solution clearer

	A	B	C	D	E	F	G	H	I	J	K
1						hcap=Y class=1 racetype=FLT racetype2=Listed	Y	1	FLT	Listed
2

<tbody>
</tbody>

Thus I have the text in F1 and I require the data after each = symbol in cells G1 trhough to J1. Hope that helps.

Regards

 

[kevatarvind]

try below udf

Function ExtractAfterSymbol(c As Range, l As Variant)
    ExtractAfterSymbol = Mid(Split(c)(l - 1), InStr(1, Split(c)(l - 1), "=") + 1, 99)
End Function

after that you can use function like below

Excel 2010
	F	G	H	I	J
1	hcap=Y class=1 racetype=FLT racetype2=Listed	Y	1	FLT	Listed
2		1	2	3	4
Input

Cell Formulas
Range	Formula
G1	=ExtractAfterSymbol($F1,G2)

 

[kevatarvind]

and if you dont want to use that 1,2,3,4 as a helper you can try like below too

Excel 2010
	F	G	H	I	J
1	hcap=Y class=1 racetype=FLT racetype2=Listed	Y	1	FLT	Listed
Input

Cell Formulas
Range	Formula
G1	=ExtractAfterSymbol($F1,COLUMN()-6)

 

[Celticshadow]

and if you dont want to use that 1,2,3,4 as a helper you can try like below too

Excel 2010

	F	G	H	I	J
1	hcap=Y class=1 racetype=FLT racetype2=Listed	Y	1	FLT	Listed

<tbody>
</tbody>

Input​

Worksheet Formulas Cell Formula G1 =ExtractAfterSymbol($F1,COLUMN()-6) <tbody> </tbody>

<tbody>
</tbody>

Hi Kevatarvind

Many thanks for replying. Unfortunately I have no idea about udf which is why I always request an excel formula to resolve my numerous problems.

Kind Regards

 

[kevatarvind]

UDF IS VERY EASY TO USE just do following

right click on sheet tab one vb window will open go to insert menu and insert module and one white screen will appear just paste my code there and save and close and after that you can use function like normal excel function which i have show you

and if u want only formula so i dont have any idea about it can be done by a single formula may be possible or some other master can do this

thanks

 

[Celticshadow]

UDF IS VERY EASY TO USE just do following

right click on sheet tab one vb window will open go to insert menu and insert module and one white screen will appear just paste my code there and save and close and after that you can use function like normal excel function which i have show you

and if u want only formula so i dont have any idea about it can be done by a single formula may be possible or some other master can do this

thanks

Hi Kevatarvind

Many thanks for replying but I really do not have a clue about udf and it is an excel formula I require as I mentioned in my first post. I appreciate your efforts and have no doubt your solution will work but I can only work with excel formula to my detriment.

Kind Regards

 

[kevatarvind]

TRY LIKE BELOW

Excel 2010
	F	G	H	I	J
1	hcap=Y class=1 racetype=FLT racetype2=Listed	Y	1	FLT	Listed
Input

Cell Formulas
Range	Formula
G1	=TRIM(RIGHT(SUBSTITUTE(TRIM(MID(SUBSTITUTE($F1," ",REPT(" ",999)),COLUMNS($F1:F1)*999-998,999)),"=",REPT(" ",99)),99))