Extract 4 character string

pto160

Active Member
Joined
Feb 1, 2009
Messages
310
Office Version
365
Platform
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?
 

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
22,886
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
Joined
Aug 23, 2010
Messages
16,344
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
Joined
Feb 1, 2009
Messages
310
Office Version
365
Platform
Windows
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
Joined
Aug 23, 2010
Messages
16,344
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
Joined
Feb 1, 2009
Messages
310
Office Version
365
Platform
Windows
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
 

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
22,886
The formula =MID(A1,FIND("z ",A1&"z ")+2,4) will do that
 

AlKey

Active Member
Joined
Oct 15, 2013
Messages
395
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
Joined
Feb 1, 2009
Messages
310
Office Version
365
Platform
Windows
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
Joined
Aug 23, 2010
Messages
16,344
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.
 

Forum statistics

Threads
1,085,703
Messages
5,385,308
Members
401,938
Latest member
JustinTaster

Some videos you may like

This Week's Hot Topics

Top