Ignore a selected alphanumeric text in a cell for the purpose of summing

STAC81

New Member
Joined
Jul 1, 2020
Messages
2
Office Version
  1. 2010
Platform
  1. Windows
I am new to the forum... Hello!

I am not sure if it is possible, but with the help of your collective expertise, hopefully we can find a solution to my issue.
We are trying to simply Sum a column, but the cells will contain Text and Numbers mixed.

We were hoping to either:

1/ Be able to select a portion of this alphanumeric text in a cell to 'ignore', while leaving a single number to Sum within the column
or
2/ Specify one of the numbers within the alphanumeric text to be the only number to sum within a cell.


The Scenario:
This is for our Production Schedule (manufacturing), where our Labor allocation (number of workers) needs to be accounted for at all times

Our different Production Lines/Machines are represented in the Rows of the spreadsheet, and the Time is represented in the Columns (each column is a half hour increment).
We 'paint' in the duration of the different jobs left to right, and have a specific format we use to identify the job details: "CUSTOMER-ITEM#-PURCHASEORDER#-#OFWORKERS"
Example of the format would be: ABCCOMPANY-08888-18500-7

What I was hoping to do to make things easier, is to sum each 30min column throughout the day, to only count the Number of Workers to ensure they are all allocated at all times of the day.

In other words, using the example above, is is possible to select a certain part of the text to ignore, to leave only a number to Sum in the column (the 7 workers in this case):
ABCCOMPANY-08888-18500-7

Or alternatively, and I specify one value to be the only one to Sum in the column? ABCCOMPANY-08888-18500-7

Thank you in advance
 

Some videos you may like

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
10,336
Welcome to the forum!

This will sum up anything after the last dash:

Book1
C
112:00
2ABCCOMPANY-08888-18500-7
3XYZCOMPANY-09999-12345-8
4WWWCOMPANY-00022-54321-5
5
6
7
8Worker sum
920
Sheet12
Cell Formulas
RangeFormula
C9C9=SUMPRODUCT(("0"&TRIM(RIGHT(SUBSTITUTE(C2:C6,"-",REPT(" ",99)),99)))+0)
 

STAC81

New Member
Joined
Jul 1, 2020
Messages
2
Office Version
  1. 2010
Platform
  1. Windows
Brilliant!!
Thank you so much Eric
 

Watch MrExcel Video

Forum statistics

Threads
1,114,013
Messages
5,545,485
Members
410,685
Latest member
chandraganji
Top