return text after 2nd occurrence of an equal sign (=)

[andymalan]

Hello
i have to extract numbers from a string in Col G (varying rows) the data looks like this

[TABLE="width: 174"]
<colgroup><col></colgroup><tbody>[TR]
[TD][TABLE="width: 268"]
<colgroup><col></colgroup><tbody>[TR]
[TD]Width = 760, Depth = 350[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD]Width = 1610, Height = 50, Depth = 16[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD]Width = 1610, Depth = 350

my formula in col H[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

MID(LEFT(G80,FIND(",",G80)-1),FIND("=",G80)+1,LEN(G80)) returns the first part i.e. 760; 1610; 1610

what formula can I use to return (in Col I) the second occurrence i.e. 350; 50; 350

your help will really be appreciated.

 

[kweaver]

I put the various numbers in H through Nth column (n = however many numbers are in the string(s) in G)

Sub ExtractNums()
Dim strg() As String, i As Integer, j As Integer, w As Long, k As Long, LR As Long
LR = Cells(Rows.Count, "G").End(xlUp).Row
For i = 1 To LR
k = 8
strg = Split(Range("G" & i), ",")
 For j = 0 To UBound(strg)
 w = WorksheetFunction.Find("=", strg(j))
 Cells(i, k) = 1 * Right(strg(j), Len(strg(j)) - w)
 k = k + 1
 Next j
Next i
End Sub

 

[AhoyNC]

With a formula.

Excel Workbook
	G	H	I
1	Width = 760, Depth = 350		350
2	Width = 1610, Height = 50, Depth = 16		50
3	Width = 1610, Depth = 350		350
Sheet

 

[jtakw]

Hi,

If, according to your description, you don't care for the Order of the data (i.e. Width, Depth, Height), you Just want the 1st, 2nd, and 3rd numeric values extracted, use this in Column H, copied down and across to Column J, results are converted to Real numbers:

Book1
	G	H	I	J
1	Width = 760, Depth = 350	760	350
2	Width = 1610, Height = 50, Depth = 16	1610	50	16
3	Width = 1610, Depth = 350	1610	350
Sheet702

Cell Formulas
Range	Formula
H1	=IFERROR(MID(SUBSTITUTE(SUBSTITUTE($G1,"=",REPT(" ",100)),",",REPT(" ",100)),(COLUMNS($H1:H1)*2-1)*100,100)+0,"")

PS, formula can be copied across to as many Columns as you need if there are more than 3 numbers in the original text string.

 

[trunten]

With your example of Cell G80

Column H:
=VALUE(MID(LEFT(G80,FIND(",",G80)-1),FIND("=",G80)+1,LEN(G80)))

Column I:
=VALUE(MID(G80,FIND("=",SUBSTITUTE(G80,"="," ",1))+1,LEN(G80)))

 

[andymalan]

Thank you very much, really appreciate your effort.

 

[andymalan]

Thank you so much.☺

 

[DanteAmor]

Another way

<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:225.27px;" /><col style="width:155.88px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >G</td><td >H</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td >Width = 760, Depth = 350</td><td style="text-align:right; ">350</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td >Width = 1610, Height = 50, Depth = 16</td><td style="text-align:right; ">50</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td >Width = 1610, Depth = 350</td><td style="text-align:right; ">350</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 ></td><td ></td></tr><tr><td >H1</td><td >=MID(SUBSTITUTE(SUBSTITUTE(G1,",",REPT(" ",99),2),"=",REPT(" ",99),2),99,50)+0</td></tr></table></td></tr></table>