# trying to add alpha-numeric text

#### keithcuda

##### New Member
I making a spread sheet for work to track total hours worked on days off.. I am in need of a formula to add only X/16 and X/8 to equal 24, etc. I am trying to keep track of overtime hours for the entire year.

example

8
8
X/16
X/8
8
8
X/4

total hours worked on days off = 28

thanks

Last edited:

### Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

#### Rick Rothstein

##### MrExcel MVP
Give this array-entered** formula a try...

=SUM(IF(LEFT(A1:A99)="X",0+MID(A1:A99,3,2)))

**Commit this fomula using CTRL+SHIFT+ENTER and not just Enter by itself

Note: Change the two instances of 99 to row number guaranteed to be larger than the maximum row number you will ever have data in.

#### keithcuda

##### New Member
So far that seems to be working!! thank you!
could you explain to me what is going on.

#### Chris Macro

##### Well-known Member
You could use this formula beside each of your numbers (this formula targets cell A1) and it will only output the ones that start with an "x". This formula tests to see if the cell starts with an x and if it does it outputs everything after the slash.

=IF(LEFT(A1,1)="x",VALUE(MID(A1,FIND("/",A1)+1,LEN(A1))),0)

Then you could just sum up the numbers.

#### keithcuda

##### New Member
ok, What if i have this also, 4/X, 6/X that need to be added...
would it be as simple as changing left to right?

Give this array-entered** formula a try...

=SUM(IF(LEFT(A1:A99)="X",0+MID(A1:A99,3,2)))

**Commit this fomula using CTRL+SHIFT+ENTER and not just Enter by itself

Note: Change the two instances of 99 to row number guaranteed to be larger than the maximum row number you will ever have data in.

#### Rick Rothstein

##### MrExcel MVP
So far that seems to be working!! thank you!
could you explain to me what is going on.

An array formula has an array of values given to it (the range of cells A1:A99 in my example formula) and processes each element in the array of value (each cell in the cells A1:A99) individually... this produces an array of calculated values which, in my formula, are passed to the SUM function to be added up. The way the calculated values are derived is via the IF function.. it looks to see if the first character is an X or not.. if not, FALSE is returned (SUM ignores FALSE when it does its summation because FALSE is not a number) but if it is, the MID function strips away the first two characters and returns whatever follow, but that returned value is a text string which SUM would not add either, but in Excel a number in text format can be changed to a real number by involving it in a mathematical calculation, hence the addition of 0 (which will not physically change the number because adding 0 to a number simply returns that number).

#### keithcuda

##### New Member
Never mind, i figured it out... thanks for the help!!

ok, What if i have this also, 4/X, 6/X that need to be added...
would it be as simple as changing left to right?

#### keithcuda

##### New Member
I found an error, If I have a cell that contains just an "X" an error occurs. "X" representing a person with a day off that did not work it. Is there any way around this?

Give this array-entered** formula a try...

=SUM(IF(LEFT(A1:A99)="X",0+MID(A1:A99,3,2)))

**Commit this fomula using CTRL+SHIFT+ENTER and not just Enter by itself

Note: Change the two instances of 99 to row number guaranteed to be larger than the maximum row number you will ever have data in.

#### Rick Rothstein

##### MrExcel MVP
I found an error, If I have a cell that contains just an "X" an error occurs. "X" representing a person with a day off that did not work it. Is there any way around this?
Your original message did not indicate that would be a possibility. If it were to be in the format of the other X's (that is, for example, X/8) what would the number after the slash be for this "day off that did not work"... 0, 8, something else? Remember, we here know nothing about your business model, so we have no idea what X means in terms of the sum you want unless you tell us.

#### keithcuda

##### New Member
I understand that.. It was a mistake I overlooked when I was trying to explain what I was wanting. I apologize.
If it was a day off that was not worked it would be represented by an "X", X meaning zero hours worked or day off. There will always be an X, X/8, X/16, etc. But an X by itself would equal zero hours.

Replies
3
Views
392
Replies
1
Views
466
Replies
1
Views
100
Replies
3
Views
186
Replies
0
Views
63

1,195,631
Messages
6,010,785
Members
441,569
Latest member
PeggyLee

### 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.

### Which adblocker are you using?

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

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