Extract 4 character string

pto160

Active Member
Joined
Feb 1, 2009
Messages
303
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,688
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,296
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
303
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,296
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
303
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,688
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
303
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,296
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,077,918
Messages
5,337,180
Members
399,131
Latest member
Vinnyjuice

Some videos you may like

This Week's Hot Topics

Top