Extract 4 character string

pto160

Active Member
Joined
Feb 1, 2009
Messages
316
Office Version
365
Platform
Windows
Hello everyone thanks so much.
It's always a space after the "z" in this example.
Result
z123 frt abcd abcd
z1234 frt abcd abcd
Will the formula change?

I should give my real world example. My start number is not a character, it's a word. I was trying to simplify the example.
The word is "Batch". So any 4 character word or number after "Batch".
 

pto160

Active Member
Joined
Feb 1, 2009
Messages
316
Office Version
365
Platform
Windows
Quote tags didn't work properly. Here is the result?
Sheet2

AB
1 Result
2z123 frt abcdabcd
3z1234 frt abcdabcd

<colgroup> <col style="WIDTH: 30px; FONT-WEIGHT: bold"> <col style="WIDTH: 89px"> <col style="WIDTH: 64px"></colgroup> <tbody>
</tbody>


Excel tables to the web >> Excel Jeanie HTML 4
 

AlKey

Active Member
Joined
Oct 15, 2013
Messages
395
Here is another one

=IFERROR(MID(A2,SEARCH(" ???? "," "&A2&" ",FIND(" z "," "&A2)),4),"")

<b>Unknown</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="text-align: right;;"></td><td style=";">Result</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style=";">abcd z dfrgd rtyu fghj</td><td style=";">rtyu</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style=";">1234 7834 z 1456d 7364</td><td style="text-align: right;;">7364</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style=";">z erdf rtyu</td><td style=";">erdf</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style=";">z vgthn 1234 5678</td><td style="text-align: right;;">1234</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style=";">azaz 4567 abcd</td><td style=";"></td></tr></tbody></table><p style="width:3.6em;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">Sheet3</p><br /><br />
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
35,575
Office Version
2010
Platform
Windows
Here is another one

=IFERROR(MID(A2,SEARCH(" ???? "," "&A2&" ",FIND(" z "," "&A2)),4),"")

<b>Unknown</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="text-align: right;;"></td><td style=";">Result</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style=";">abcd z dfrgd rtyu fghj</td><td style=";">rtyu</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style=";">1234 7834 z 1456d 7364</td><td style="text-align: right;;">7364</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style=";">z erdf rtyu</td><td style=";">erdf</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style=";">z vgthn 1234 5678</td><td style="text-align: right;;">1234</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style=";">azaz 4567 abcd</td><td style=";"></td></tr></tbody></table><p style="width:3.6em;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">Sheet3</p><br /><br />
Excellent... nice and simple. One thing all the formulas suffer from... if there are 2 spaces after a 3-character "word" or 3 spaces after a 2-character "word", that smaller word will be returned. Using the TRIM function solves that problem (if it is, indeed, one the OP might face). Here is the fix applied to your formula...

=IFERROR(MID(TRIM(A2),SEARCH(" ???? "," "&TRIM(A2)&" ",SEARCH(" z "," "&TRIM(A2))),4),"")

Note that I also changed your FIND function call to SEARCH on the off-chance the "z" could be upper case.
 
Last edited:

oldbrewer

Board Regular
Joined
Apr 11, 2010
Messages
10,990
aaa batch 1234
batch 9999

OP now says there will always be the word batch followed by one space followed by at least 4 characters
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
35,575
Office Version
2010
Platform
Windows
aaa batch 1234
batch 9999

OP now says there will always be the word batch followed by one space followed by at least 4 characters
Sure, in Message #21 where it would be readily seen. :rolleyes:

Just replacing the letter "z" (keeping any spacing) with the word "Batch" should make the formulas work.
 
Last edited:

István Hirsch

Well-known Member
Joined
May 16, 2013
Messages
1,634
I am afraid the formulae in post #16, 17 and 18 from the string

„abcd z dfrgd r yu fghj” or „abcd z dfrgd ry u fghj”

extracts „r yu” or „ry u”, resp.,

instead of fghj.
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
35,575
Office Version
2010
Platform
Windows
I am afraid the formulae in post #16, 17 and 18 from the string

„abcd z dfrgd r yu fghj” or „abcd z dfrgd ry u fghj”

extracts „r yu” or „ry u”, resp.,

instead of fghj.
Yeah, that embedded space is problematic. Maybe the answer is a UDF (user defined function) instead...

Code:
Function FourChars(S As String) As String
  Dim X As Long, Txt() As String
  Txt = Split(Split(" " & S, " z ", , vbTextCompare)(1))
  For X = 0 To UBound(Txt)
    If Txt(X) Like "[A-Za-z0-9][A-Za-z0-9][A-Za-z0-9][A-Za-z0-9]" Then
      FourChars = Txt(X)
      Exit Function
    End If
  Next
End Function

HOW TO INSTALL UDFs
------------------------------------
If you are new to UDFs, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the above code into the code window that just opened up. That's it.... you are done. You can now use FourChars just like it was a built-in Excel function. For example,

=FourChars(A1)

If you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm) and answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.
 
Last edited:

pto160

Active Member
Joined
Feb 1, 2009
Messages
316
Office Version
365
Platform
Windows
Hello, thanks so much for the help here.
What do you mean by embedded space?
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
35,575
Office Version
2010
Platform
Windows
What do you mean by embedded space?
The " ???? " in those SEARCH function calls are looking for 4 characters in a row surrounded by spaces, but those 4 characters can be anything, including spaces., The "embedded space in " AB C " would not stop the "AB C" part from matching the "????" wildcards, hence, the formulas fail.
 

Forum statistics

Threads
1,086,110
Messages
5,387,888
Members
402,086
Latest member
vlmedia

Some videos you may like

This Week's Hot Topics

Top