Formula Streamline

[Biz]

Dear All,

I worked a solution but I am sure Excel formula gurus and get better solution then me.

Start from cell B10
<TABLE style="WIDTH: 48pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=64><COLGROUP><COL style="WIDTH: 48pt" width=64><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 48pt; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=17 width=64>3456 Sales</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=17>12 Cars</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=17>rtsysy</TD></TR></TBODY></TABLE>
Formula in Cell A10 I used formula below

=IF(ISNUMBER(IF(ISERROR(FIND(" ",B10)),B10,LEFT(B10,FIND(" ",B10)-1)+0)),IF(OR(IF(ISERROR(FIND(" ",B10)),B10,LEFT(B10,FIND(" ",B10)-1)+0)=30,IF(ISERROR(FIND(" ",B10)),B10,LEFT(B10,FIND(" ",B10)-1)+0)=34),"Combined 30 & 34",IF(LEN(IF(ISERROR(FIND(" ",B10)),B10,LEFT(B10,FIND(" ",B10)-1))+0)=2,IF(ISERROR(FIND(" ",B10)),B10,LEFT(B10,FIND(" ",B10)-1)+0),"")),"")

Solution A10 and down would be
the only solution 12 in cell A12 and the first word is two digit numbers and others are not so blanks " "

Kind Regards

Biz

 

[Biz]

Hi,

The another problem is if I try to record formula above in vba it does not work.

   ActiveCell.FormulaR1C1 = _
        "=IF(ISNUMBER(IF(ISERROR(FIND("" "",RC[1])),RC[1],LEFT(RC[1],FIND("" "",RC[1])-1)+0)),IF(OR(IF(ISERROR(FIND("" "",RC[1])),RC[1],LEFT(RC[1],FIND("" "",RC[1])-1)+0)=30,IF(ISERROR(FIND("" "",RC[1])),RC[1],LEFT(RC[1],FIND("" "",RC[1])-1)+0)=34),""Combined 30 & 34"",IF(LEN(IF(ISERROR(FIND("" "",RC[1])),RC[1],LEFT(RC[1],FIND("" "",RC[1])-1))+0)=2,IF(ISERROR(FIND("" "",RC[1" & _
        "],LEFT(RC[1],FIND("" "",RC[1])-1)+0),"""")),"""")"

I get runtime error '1004' Application -defined or Object-defined error.

Biz

 

[Giordano Bruno]

Does this work?

=IF(ISERROR(FIND(" ",B10)),"",VALUE(LEFT(B10,2)))

 

[Biz]

Not really. It is bit twisted.

1) Extract first word and then check length of first word
2) If first word len =2 and it isnumber then extract display first word.
3) If first word len>2 then blank ""

Biz

 

[T. Valko]

Dear All,

I worked a solution but I am sure Excel formula gurus and get better solution then me.

Start from cell B10
<TABLE style="WIDTH: 48pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=64 border=0><COLGROUP><COL style="WIDTH: 48pt" width=64><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; WIDTH: 48pt; BORDER-BOTTOM: #f0f0f0; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=64 height=17>3456 Sales</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>12 Cars</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>rtsysy</TD></TR></TBODY></TABLE>
Formula in Cell A10 I used formula below

=IF(ISNUMBER(IF(ISERROR(FIND(" ",B10)),B10,LEFT(B10,FIND(" ",B10)-1)+0)),IF(OR(IF(ISERROR(FIND(" ",B10)),B10,LEFT(B10,FIND(" ",B10)-1)+0)=30,IF(ISERROR(FIND(" ",B10)),B10,LEFT(B10,FIND(" ",B10)-1)+0)=34),"Combined 30 & 34",IF(LEN(IF(ISERROR(FIND(" ",B10)),B10,LEFT(B10,FIND(" ",B10)-1))+0)=2,IF(ISERROR(FIND(" ",B10)),B10,LEFT(B10,FIND(" ",B10)-1)+0),"")),"")

Solution A10 and down would be
the only solution 12 in cell A12 and the first word is two digit numbers and others are not so blanks " "

Kind Regards

Biz

Maybe this...

=IF(COUNT(FIND(" ",B10)),IF(COUNT(--LEFT(B10,FIND(" ",B10))),--LEFT(B10,FIND(" ",B10)),""),"")

 

[shg]

Biz, that string resolves to

=IF(ISNUMBER(IF(ISERROR(FIND(" ",RC[1])),RC[1],LEFT(RC[1],FIND(" ",RC[1])-1)+0)),
       IF(OR(IF(ISERROR(FIND(" ",RC[1])),RC[1],LEFT(RC[1],FIND(" ",RC[1])-1)+0)=30,
             IF(ISERROR(FIND(" ",RC[1])),RC[1],LEFT(RC[1],FIND(" ",RC[1])-1)+0)=34),"Combined 30 & 34",
      IF(LEN(IF(ISERROR(FIND(" ",RC[1])),RC[1],LEFT(RC[1],FIND(" ",RC[1])-1))+0)=2,
             IF(ISERROR(FIND(" ",RC[1],        LEFT(RC[1],FIND(" ",RC[1])-1)+0),"")),"")

If you put Excel in R1C1 mode, you can't enter it manually, either; it has an error.

 

[Biz]

Maybe this...

=IF(COUNT(FIND(" ",B10)),IF(COUNT(--LEFT(B10,FIND(" ",B10))),--LEFT(B10,FIND(" ",B10)),""),"")

Hi Biff

Sorry forgot to mention
84155 Container 20ft
the answer "".

Biz

 

[Biz]

Biz, that string resolves to

=IF(ISNUMBER(IF(ISERROR(FIND(" ",RC[1])),RC[1],LEFT(RC[1],FIND(" ",RC[1])-1)+0)),
       IF(OR(IF(ISERROR(FIND(" ",RC[1])),RC[1],LEFT(RC[1],FIND(" ",RC[1])-1)+0)=30,
             IF(ISERROR(FIND(" ",RC[1])),RC[1],LEFT(RC[1],FIND(" ",RC[1])-1)+0)=34),"Combined 30 & 34",
      IF(LEN(IF(ISERROR(FIND(" ",RC[1])),RC[1],LEFT(RC[1],FIND(" ",RC[1])-1))+0)=2,
             IF(ISERROR(FIND(" ",RC[1], LEFT(RC[1],FIND(" ",RC[1])-1)+0),"")),"")

If you put Excel in R1C1 mode, you can't enter it manually, either; it has an error.

I tried your suggestion but it does not work. If you tried converting my formula in 1st post to vba does recorded code work?
I tried recording it but it does not work.

 

[T. Valko]

Not really. It is bit twisted.

1) Extract first word and then check length of first word
2) If first word len =2 and it isnumber then extract display first word.
3) If first word len>2 then blank ""

Biz

Hi Biff

Sorry forgot to mention
84155 Container 20ft
the answer "".

Biz

Maybe this...

=IF(MID(B10,3,1)=" ",IF(COUNT(--LEFT(B10,2)),--LEFT(B10,2),""),"")

 

[Biz]

Maybe this...

=IF(MID(B10,3,1)=" ",IF(COUNT(--LEFT(B10,2)),--LEFT(B10,2),""),"")

Hi Biff,

If you get 30 or 34 then I need "Combined 30 & 34". I tried in my 1st post but my formula become too large.

Biz