Using "mid" between two characters

sroot

New Member
Joined
Dec 19, 2013
Messages
8
I have alot of data i am trying to sort into excel and i think i need to use the mid function just not sure how to type this one out right... Here is my example "Variable PCARD0-V9R 2014-08-05 20140711 69.36" assuming this is in A1 what would i do to get everything from 2014 to the last space (this information can always change but it will always be in between a space and the dollar figure) so for this one i would want in another column 2014-08-05 20140711 Any ideas? Thanks!
 

Some videos you may like

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

jduley

Board Regular
Joined
May 8, 2014
Messages
64
How about this.
=MID(A1,FIND(" ",A1)+1,(FIND("~",SUBSTITUTE(A1," ","~",LEN(A1)-LEN(SUBSTITUTE(A1," ","")))))-FIND(" ",A1))
 

sroot

New Member
Joined
Dec 19, 2013
Messages
8
That returns PCARD0-V9R 2014-08-05 20140711 i just need 2014-08-05 20140711
 

jduley

Board Regular
Joined
May 8, 2014
Messages
64
Sorry, I missed that "Variable" was part of the string. The formula I posted will not start at the correct position as a result.
 

jduley

Board Regular
Joined
May 8, 2014
Messages
64

ADVERTISEMENT

Try =MID(A1,FIND(" ",A1,FIND(" ",A1)+1)+1,(FIND("~",SUBSTITUTE(A1," ","~",LEN(A1)-LEN(SUBSTITUTE(A1," ","")))))-FIND(" ",A1,FIND(" ",A1)+1))
 

skywriter

Well-known Member
Joined
Feb 15, 2014
Messages
1,642
Here's another solution:

<b>Excel 2010</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB"><colgroup><col width="25px" style="background-color: #DAE7F5" /><col /><col /></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th></th><th>A</th><th>B</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style=";">Variable PCARD0-V9R 2014-08-05 20140711 69.36</td><td style=";">2014-08-05 20140711</td></tr></tbody></table><p style="width:4.2em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #BBB;border-top:none;text-align: center;background-color: #DAE7F5;color: #161120">Sheet43</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #BBB"><thead><tr style=" background-color: #DAE7F5;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">B1</th><td style="text-align:left">=LEFT(<font color="Blue">RIGHT(<font color="Red">A1,LEN(<font color="Green">A1</font>)-FIND(<font color="Green">"2014",A1,1</font>)+1</font>),FIND(<font color="Red">"|",SUBSTITUTE(<font color="Green">RIGHT(<font color="Purple">A1,LEN(<font color="Teal">A1</font>)-FIND(<font color="Teal">"2014",A1,1</font>)+1</font>)," ","|",2</font>),1</font>)-1</font>)</td></tr></tbody></table></td></tr></table><br />
 

skywriter

Well-known Member
Joined
Feb 15, 2014
Messages
1,642
Disregard my above post. After looking at your request again, I see I mistakenly thought data you wanted to abstract always started with 2014.
 

Watch MrExcel Video

Forum statistics

Threads
1,109,549
Messages
5,529,471
Members
409,884
Latest member
Msinmath
Top