Mid

[rhmkrmi]

Hello,

How can I use MID for find a number of characters to the left of where MID starts? It always gives you a number of characters to the right of where is starts!

Thank you

 

[dmartin35]

I may not understand what you are asking, but it sounds like you would use the LEFT function. That will give you the number of characters from the left of the string up to the number you specify. So if you wanted the left 3 characters of the string in A1 you would use:

<code>
=LEFT(A1,3)
</code>

 

[Micron]

MID(A1,5,12) will start at the 5th character and return 12. So if you start at "5", doesn't that tell you there are 4 characters to the left? Post an example of what you've tried, or subtract the value of the length of MID from the length of the entire string using Len (2x) and Mid.

 

[rhmkrmi]

Thanks,

I am trying to extract 18/2/15 from text "Book # 1279217 - 18/2/15" in a cell.

Please note that the text is not consistent but what I want to extract is a date where / has been used all along!

 

[Rick Rothstein]

Hello,

How can I use MID for find a number of characters to the left of where MID starts? It always gives you a number of characters to the right of where is starts!

The second argument is the starting position that the function will use to retrieve text at, so if you want to move the starting point to before that, just subtract the number of characters from your current starting position.

 

[Micron]

Still not enough info. You need to know what the constants are first so that you have something reliable to work with.
Will the date always have 2 characters before the first / ?
Will the length of the date differ (you have 7, what about 25/10/15)?
Will there always be a dash and space before the first character of the date?
Take a look at Extract numbers from text in excel - How to & Tutorial | Chandoo.org - Learn Microsoft Excel Online

 

[rhmkrmi]

Thanks,

The dates all have slash in them but they do not always appear at the beginning of the text, here are some examples:

Copy # 178499 - 11/5/15
1258736 - Dated 25/2/15
21/01/2014 inv 1258950
Invoice #125444 21/11/2014 - refer order 58747

I am trying to extract the date, I thought to search for "/" and them use the mid but am missing the characters before the "/" being the days!

 

[Micron]

Basically, I think you're beat because you have a mish-mash of date possibilities - maybe 2015, maybe 15. Maybe 11/5/yy, and who knows - maybe 11/05/yy. And maybe some text after the date. I doubt you will find a way that is 100% reliable because of too many variables.
This should retrieve the date for a string like 1258736 - Dated 25/2/15 :=MID(A2,FIND("/",A2,1)-2,7)