Extract 4 character string

pto160

Active Member
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?
 

mikerickson

MrExcel MVP
How do you determine which four character string to extract?

In general, the format would be =MID(A1, StartCharacter, 4), but what criteria are you using to determine what StartCharacter is?
 

Marcelo Branco

MrExcel MVP
A possible solution with formulas
Remark: Observe the result in B5 - the formula extracts only the first string with 4 characters, case there are more than one


A
B
1
Text​
Result​
2
abc rtyidf 1034 567​
1034​
3
fg abcd 789 1464654​
abcd​
4
erd f rtyu​
rtyu​
5
vgthn 1234 1234​
1234​

<tbody>
</tbody>


Array formula in B2 copied down
=MID(A2,SEARCH("@",SUBSTITUTE(A2," ","@",MATCH(TRUE,LEN(TRIM(MID(SUBSTITUTE(" "&A2," ",REPT(" ",200)),(ROW($A$1:$A$100)-ROW($A$1)+1)*200,200)))=4,0)-1))+1,4)

confirmed with Ctrl+Shift+Enter, not just Enter

Hope this helps

M.
 

pto160

Active Member
Thank you. It works fantastic. To the point that mikerickson made, if I wanted the start character to be z is there a way to do that?

abc 1034 rtyidf z 7896
Result:
7896


<tbody>
</tbody>
 

Marcelo Branco

MrExcel MVP
Thank you. It works fantastic. To the point that mikerickson made, if I wanted the start character to be z is there a way to do that?

abc 1034 rtyidf z 7896
Result:
7896

<tbody>
</tbody>
We need more examples. Could you provide?

M.
 

pto160

Active Member
I want to find the 4 character string that starts at z. Here is an example:
Sheet1

AB
1 Result z character
2abcd z dfrg rtyu fghjdfrg
31234 7834 z 1456 73641456
4z erdf rtyuerdf

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


Excel tables to the web >> Excel Jeanie HTML 4
 

AlKey

Active Member
Try this

=MID(A2,SEARCH("z",A2)+2,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 z character</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style=";">abcd z dfrg rtyu fghj</td><td style="text-align: center;;">dfrg</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style=";">1234 7834 z 1456 7364</td><td style="text-align: center;;">1456</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style=";">z erdf rtyu</td><td style="text-align: center;;">erdf</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">Sheet2</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">B2</th><td style="text-align:left">=MID(<font color="#0000FF">A2,SEARCH(<font color="#FF0000">"z",A2</font>)+2,4</font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">B3</th><td style="text-align:left">=MID(<font color="#0000FF">A3,SEARCH(<font color="#FF0000">"z",A3</font>)+2,4</font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">B4</th><td style="text-align:left">=MID(<font color="#0000FF">A4,SEARCH(<font color="#FF0000">"z",A4</font>)+2,4</font>)</td></tr></tbody></table></td></tr></table><br />
 

pto160

Active Member
I should have been more clear with my explanation and examples. I'm looking for the first 4 character word or number that starts at z.
Sheet1

AB
1 Result z character
2abcd z dfrgd rtyu fghjrtyu
31234 7834 z 1456d 73647364
4z erdf rtyuerdf

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


Excel tables to the web >> Excel Jeanie HTML 4
 

Marcelo Branco

MrExcel MVP
A possible solution with formulas
Remark: Observe the result in B5 - the formula extracts only the first string with 4 characters, case there are more than one


A
B
1
Text​
Result​
2
abc rtyidf 1034 567​
1034​
3
fg abcd 789 1464654​
abcd​
4
erd f rtyu​
rtyu​
5
vgthn 1234 1234​
1234​

<tbody>
</tbody>


Array formula in B2 copied down
=MID(A2,SEARCH("@",SUBSTITUTE(A2," ","@",MATCH(TRUE,LEN(TRIM(MID(SUBSTITUTE(" "&A2," ",REPT(" ",200)),(ROW($A$1:$A$100)-ROW($A$1)+1)*200,200)))=4,0)-1))+1,4)

confirmed with Ctrl+Shift+Enter, not just Enter
Oops...
Sorry, this formula has a flaw - it fails when the first piece of the text has 4 characters.

Use this new version instead
=MID(A2,SEARCH("@",SUBSTITUTE(" "&A2," ","@",MATCH(TRUE,LEN(TRIM(MID(SUBSTITUTE(" "&A2," ",REPT(" ",200)),(ROW($A$1:$A$100)-ROW($A$1)+1)*200,200)))=4,0))),4)

Ctrl+Shift+Enter

M.
 

Some videos you may like

This Week's Hot Topics

  • Importing multiple excel files into one spreadsheet
    Hi, I'm trying to import multiple excel files (with the same format into a single spreadsheet) so that each day's file is listed underneath the...
  • find many based on a certain criteria
    good evening, I hope someone can help me? I have a workbook sheet 2 contains lots of data.... I would like to be able to find anything on sheet...
  • How to copy multiple rows using If
    Hi all, I'm very new to VBA and have written this simple code to copy certain cells if a certain cell within that row contains any data. I need...
  • VBA If statement
    Dear All, I have two dates, where I'd like a message box to pop, if the dates are between this criteria. [CODE] sDate1 = #10/1/2019#...
  • Text Format
    I have a sheet for user to keyin the data. The format of the data can be 451 / 1903, 0012 / 9908 or 00287 / 0099. The number after the "/" is...
  • Syntax errors
    Good Morning, Trying to compile a workbook, I keep getting a few errors. Here are the first two: [code=rich]Syntax Error: Function...
Top