# can someone explain this formula?

#### oneway

##### New Member
=VALUE(MID(K29,10,2))

I'm using a templete that was given to me by a vender. When the cells are filled it puts the data in a chart. All other cells work fine but the three that have this formula doesn't.

Thanks

### Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK
Welcome to MrExcel -- you can find good descriptions of the MID and VALUE functions in Excel's help file, but your formula breaks down as --

MID(K29,10,2) == Grab the MIDdle characters from cell K29's value, begining with the 10th character and going 2 characters in length.

=VALUE(...) == As MID returns a String, the VALUE function is used to convert its argument to the numeric equivalent.

Could also use

=--MID(K29,10,2)

or

=MID(K29,10,2)+0

Hi oneway, welcome to the board!

I know this may not be what you want to hear, but the Help files are really great for things like this. The only other thing you need to understand is that the inner-most parens are worked on first, so the MID function is calculated before the VALUE function. Another good tool is the formula auditor.

-----------------------------------------------------------------------------

The Help Files...

-----------------------------------------------------------------------------

VALUE

Converts a text string that represents a number to a number.

Syntax

VALUE(text)

Text is the text enclosed in quotation marks or a reference to a cell containing the text you want to convert.

Remarks

• Text can be in any of the constant number, date, or time formats recognized by Microsoft Excel. If text is not in one of these formats, VALUE returns the #VALUE! error value.
• You do not generally need to use the VALUE function in a formula because Excel automatically converts text to numbers as necessary. This function is provided for compatibility with other spreadsheet programs.

-----------------------------------------------------------------------------

MID

Also applies to:

MIDB

MID returns a specific number of characters from a text string, starting at the position you specify, based on the number of characters you specify.

MIDB returns a specific number of characters from a text string, starting at the position you specify, based on the number of bytes you specify. This function is for use with double-byte characters.

Syntax

MID(text,start_num,num_chars)

MIDB(text,start_num,num_bytes)

Text is the text string containing the characters you want to extract.

Start_num is the position of the first character you want to extract in text. The first character in text has start_num 1, and so on.

Num_chars specifies the number of characters you want MID to return from text.

Num_bytes specifies the number of characters you want MIDB to return from text, in bytes.

Remarks

• If start_num is greater than the length of text, MID returns "" (empty text).
• If start_num is less than the length of text, but start_num plus num_chars exceeds the length of text, MID returns the characters up to the end of text.
• If start_num is less than 1, MID returns the #VALUE! error value.
• If num_chars is negative, MID returns the #VALUE! error value.
• If num_bytes is negative, MIDB returns the #VALUE! error value.
[/list]

Thanks guys, it make sense now.
I'll try to remember to look in the help files next time, I've only been working with excel for a couple of months so I'm sure I'll have many question.

Thanks again.

Replies
1
Views
452
Replies
2
Views
204
Replies
6
Views
387
Replies
3
Views
226
Replies
9
Views
357

1,203,605
Messages
6,056,229
Members
444,852
Latest member
MJaspering

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