Using IF,AND + LEN

[jarrodexcel]

So i have a cell with product codes inside of that. for example
cell a1 contains data - 1x ddjed8cx7ee
cell a2 contains data - 4x ddjeudaadi3
cell a3 contains data - 1x nddnadjnfke 1x ajo37danunau 4x aud73ndnnak
and so on

I am trying to find a formula that can
A. return the value of 30 if cell contains the character (1x)
B. multiply this value of 30 by how many times there are... ie 2x will return 60. 5x returns 150 and so on
C. Due to the way the data is downloaded into my excel sheet, the data is put with lines if there is multiple data per cell. so in cell A if there is more than 1 set of product codes it will put them all in a drop down line like this
1x nubd77e87e
1x11683638731
2xeufw87h32h22
and so on.
so the total of this cell would have 4x in it and should return the value of 120

Any help what so ever would be so appreciated, i can provide more detail if required as im sure ive not explained this well enough, unless you're some excel wizard.

 

[JamesCanale]

=LET(a,TEXTSPLIT(A1," "),b,VALUE(LEFT(a,LEN(a)-1)),c,FILTER(b,ISNUMBER(b)),SUM(c)*20)

 

[Fluff]

Hi & welcome to MrExcel.
Another option

Fluff.xlsm
	A	B
1
2	1x nubd77e87e 1x 11683638731 2x eufw87h32h22	120
Data

Cell Formulas
Range	Formula
B2	B2	=SUM(--TAKE(TEXTSPLIT(A2,"x ",CHAR(10)),,1))*30

 

[jarrodexcel]

Hi & welcome to MrExcel.
Another option

Fluff.xlsm
	A	B
1
2	1x nubd77e87e 1x 11683638731 2x eufw87h32h22	120
Data

Cell Formulas
Range	Formula
B2	B2	=SUM(--TAKE(TEXTSPLIT(A2,"x ",CHAR(10)),,1))*30

Perfect, this helped a bunch !! absolute wizard

 

[Fluff]

Glad we could help & thanks for the feedback.