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?
 

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.
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?
 
Upvote 0
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.
 
Upvote 0
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>
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
Try this

=MID(A2,SEARCH("z",A2)+2,4)


Unknown
AB
1Result z character
2abcd z dfrg rtyu fghjdfrg
31234 7834 z 1456 73641456
4z erdf rtyuerdf
Sheet2
Cell Formulas
RangeFormula
B2=MID(A2,SEARCH("z",A2)+2,4)
B3=MID(A3,SEARCH("z",A3)+2,4)
B4=MID(A4,SEARCH("z",A4)+2,4)
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,567
Messages
6,114,342
Members
448,570
Latest member
rik81h

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