Mid function (1 Viewer)

Brianwarnock · Sep 16, 2014

I learnt something today, that when using the mid function on a worksheet you must specify the third parameter, I have only used it in VBA till now where omission of the third parameter causes mid to extract the rest of the field from the start point.

Just thought that I would mention this as I have always stated the later position as fact without conditions.

Brian

Rx_ · Sep 16, 2014

I beleive the 3rd argument to Mid is not the position to stop at, but the number of characters to return from the starting position.

So, if you eliminate the 3rd argument it returns from the starting position to the end of the string.

attached is an example of the Mid function for Cells in standard and in array format.
When remotely programming Excel from Access VBA, I often prefer the array format. It is easier to add variables and put into a loop.

Brianwarnock · Sep 16, 2014

The point RX is that it didn't give me the rest when the third parameter was omitted, it gave a error, incorrect number of parameters. I was trying to extract forenames following the title.

Mid(a1,Find(a1," ")+1)
Gave the error so I had to resort to using Len etc to give the length of the text
I'm on 2013 so if it works in earlier releases well

It was a shock as I had used the technic many times in VBA

Brian

Rx_ · Sep 16, 2014

I tried it in Excel 2010 and got the same error (missing 3rd parameter) in the Workbook mode. The third parameter was required.

It is kind of funny how dozens of sites say it is not required (or is optional).

Now in the Excel Module1 vba code, it does show as being optional.
Public Function MyFirstName(Myvar As String) As String
MyFirstName = Mid(Myvar, InStr(Myvar, " "))
End Function
This worked in Excel 2010 with or without the last parameter.
I used Instr because the Search wouldn't work in VBA modules.

anyway, that is what I found.

Brianwarnock · Sep 17, 2014

Now done what I should have done in the first place and thus made my first post more complete, look at Help.

For VBA

length

Optional; Variant (Long). Number of characters to return. If omitted or if there are fewer than length characters in the text (including the character at start), all characters from the start position to the end of the string are returned.

For the Worksheet Function

Num_chars Required. Specifies the number of characters you want MID to return from text.
Num_bytes Required. Specifies the number of characters you want MIDB to return from text, in bytes.

Brian

kevlray · Sep 17, 2014

It is interesting (in Excel 2010). If you use the mid formula on a sheet it requires the three arguments, but if you use VBA code it does not.

Brianwarnock · Sep 17, 2014

Kev did you read the thread? The answer is in there already stated and I've quoted the help confirming the difference between VBA and worksheet.

Brian

kevlray · Sep 18, 2014

Opps, I guess I scanned too fast through the thread.

Mid function (1 Viewer)

Brianwarnock

Retired

Rx_

Nothing In Moderation

Attachments

Brianwarnock

Retired

Rx_

Nothing In Moderation

Brianwarnock

Retired

kevlray

Registered User.

Brianwarnock

Retired

kevlray

Registered User.

Similar threads

Users who are viewing this thread