Extract 4 character string

pto160

Active Member
Joined
Feb 1, 2009
Messages
473
Office Version
  1. 365
Platform
  1. Windows
I'm trying to extract a 4 character string that can be numbers or a text string. Here is the example.

Sheet1

AB
1 Result
2abc rtyidf 1034 567
1034
3fg abcd 789 1464654abcd
4erd f rtyurtyu

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


Excel tables to the web >> Excel Jeanie HTML 4

How do you do it?
 
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".
 
Upvote 0

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.
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
 
Upvote 0
Here is another one

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


Unknown
AB
1Result
2abcd z dfrgd rtyu fghjrtyu
31234 7834 z 1456d 73647364
4z erdf rtyuerdf
5z vgthn 1234 56781234
6azaz 4567 abcd
Sheet3
 
Upvote 0
Here is another one

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


Unknown
AB
1Result
2abcd z dfrgd rtyu fghjrtyu
31234 7834 z 1456d 73647364
4z erdf rtyuerdf
5z vgthn 1234 56781234
6azaz 4567 abcd
Sheet3
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:
Upvote 0
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
 
Upvote 0
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:
Upvote 0
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.
 
Upvote 0
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:
Upvote 0
Hello, thanks so much for the help here.
What do you mean by embedded space?
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,521
Messages
6,114,104
Members
448,548
Latest member
harryls

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top