Formula Streamline

B

Biz

Well-known Member
Joined
May 18, 2009
Messages
1,773
Office Version
  1. 2021
Platform
  1. Windows
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
Code: 
=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
 
Sort by date Sort by votes
Hi Biff,

Got your formula working now.
Code: 
=IF(MID(B10,3,1)=" ",IF(COUNT(--LEFT(B10,2)),IF(OR(--LEFT(B10,2)=30,--LEFT(B10,2)=30),"B",--LEFT(B10,2))),"")


Biz
 
Upvote 0

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.
Biz said:
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
Click to expand...
We'll get it sooner or later! :)

How about this...

Book1
BC
103456_
11Sales_
1212 Cars12
1330 rtsysyCombined 30 & 34
14xx xx_
1584155 Container 20ft_
Summary

Formula entered in C10 and copied down:

=IF(MID(B10,3,1)=" ",IF(COUNT(--LEFT(B10,2)),IF(OR(--LEFT(B10,2)={30,34}),"Combined 30 & 34",--LEFT(B10,2)),""),"")
 
Upvote 0
T. Valko said:
We'll get it sooner or later! :)

How about this...

Summary

<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Verdana,Arial; FONT-SIZE: 10pt" border=0 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 149px"><COL style="WIDTH: 127px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD> </TD><TD>B</TD><TD>C</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">10</TD><TD style="BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: left; BORDER-LEFT: #000000 1px solid; FONT-FAMILY: Arial; BORDER-TOP: #000000 1px solid; BORDER-RIGHT: #000000 1px solid">3456</TD><TD style="BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: left; COLOR: #ffffff; BORDER-LEFT-COLOR: #000000; BORDER-TOP: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid">_</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">11</TD><TD style="BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: left; BORDER-LEFT: #000000 1px solid; BORDER-TOP-COLOR: #000000; BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid">Sales</TD><TD style="BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: left; BORDER-TOP-COLOR: #000000; BORDER-TOP-WIDTH: 1px; COLOR: #ffffff; BORDER-LEFT-COLOR: #000000; BORDER-LEFT-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid">_</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">12</TD><TD style="BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: left; BORDER-LEFT: #000000 1px solid; BORDER-TOP-COLOR: #000000; BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid">12 Cars</TD><TD style="BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: left; BORDER-TOP-COLOR: #000000; BORDER-TOP-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-LEFT-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid">12</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">13</TD><TD style="BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: left; BORDER-LEFT: #000000 1px solid; BORDER-TOP-COLOR: #000000; BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid">30 rtsysy</TD><TD style="BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: left; BORDER-TOP-COLOR: #000000; BORDER-TOP-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-LEFT-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid">Combined 30 & 34</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">14</TD><TD style="BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: left; BORDER-LEFT: #000000 1px solid; BORDER-TOP-COLOR: #000000; BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid">xx xx</TD><TD style="BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: left; BORDER-TOP-COLOR: #000000; BORDER-TOP-WIDTH: 1px; COLOR: #ffffff; BORDER-LEFT-COLOR: #000000; BORDER-LEFT-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid">_</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">15</TD><TD style="BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: left; BORDER-LEFT: #000000 1px solid; BORDER-TOP-COLOR: #000000; BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid">84155 Container 20ft</TD><TD style="BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: left; BORDER-TOP-COLOR: #000000; BORDER-TOP-WIDTH: 1px; COLOR: #ffffff; BORDER-LEFT-COLOR: #000000; BORDER-LEFT-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid">_</TD></TR></TBODY></TABLE>


Formula entered in C10 and copied down:

=IF(MID(B10,3,1)=" ",IF(COUNT(--LEFT(B10,2)),IF(OR(--LEFT(B10,2)={30,34}),"Combined 30 & 34",--LEFT(B10,2)),""),"")
Click to expand...


Hi Biff,

Very classy solution mate!

My solution is so old school post 1 and even adapting your formula is still not classy

Code: 
[SIZE=3][FONT=Calibri]=IF(MID(B10,3,1)=" ",IF(COUNT(--LEFT(B10,2)),IF(OR(--LEFT(B10,2)=30,--LEFT(B10,2)=30),"Combined 30 & 34",--LEFT(B10,2))),"")<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" /><o:p></o:p>[/FONT][/SIZE]

Thank you very much for helping me out. I managed to record the formula in vba and now everything works like charm.

Biz
 
Upvote 0
Biz said:
Hi Biff,

Very classy solution mate!

My solution is so old school post 1 and even adapting your formula is still not classy

Code: 
[SIZE=3][FONT=Calibri]=IF(MID(B10,3,1)=" ",IF(COUNT(--LEFT(B10,2)),IF(OR(--LEFT(B10,2)=30,--LEFT(B10,2)=30),"Combined 30 & 34",--LEFT(B10,2))),"")<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" /><o:p></o:p>[/FONT][/SIZE]

Thank you very much for helping me out. I managed to record the formula in vba and now everything works like charm.

Biz
Click to expand...
Good deal. Thanks for the feedback! :cool:
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Solved
VBA Function to Paste Table into Outlook Email Resulting in HTML language in Email
Replies
8
Views
623
smozgur
smozgur
P
  • Question Question
RUNTIME ERROR 91 for USERFORM CODE
Replies
1
Views
113
6StringJazzer
6StringJazzer
V
  • Question Question
How to Expand Components of an Assembly while preserving Dates
Replies
2
Views
76
Vog2654
V
O
  • Solved
VBA - floating photo
Replies
3
Views
495
RoryA
RoryA
A
  • Solved
Extract the require data from Sheet1 to Sheet2 (Total Consumption For an Item for a period in Sheet1 For Several Items) in another sheet2 get in deta
2
Replies
10
Views
223
DanteAmor
DanteAmor

Forum statistics

Threads
1,224,596
Messages
6,179,807
Members
452,944
Latest member
2558216095

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top