Extract 4 character string

Marcelo Branco

MrExcel MVP
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

 A B 1 Result z character 2 abcd z dfrgd rtyu fghj rtyu 3 1234 7834 z 1456d 7364 7364 4 z erdf rtyu erdf

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4
Maybe this..

 A​ B​ C​ 1​ Text​ Helper​ Result​ 2​ abcd z dfrgd rtyu fghj​ z dfrgd rtyu fghj​ rtyu​ 3​ 1234 7834 z 1456d 7364​ z 1456d 7364​ 7364​ 4​ z erdf rtyu​ z erdf rtyu​ erdf​ 5​ vgthn 1234 5678​ vgthn 1234 5678​ 1234​

<tbody>
</tbody>

Formula in B2 copied down (Helper)
=IFERROR(MID(A2,SEARCH("z",A2),200),A2)

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

Ctrl+Shift+Enter

M.

Rick Rothstein

MrExcel MVP
Formula in B2 copied down (Helper)
=IFERROR(MID(A2,SEARCH("z",A2),200),A2)
Given the OP's examples, maybe your search should be for " z " (space/z/space) instead of just "z", otherwise you might pick up an embedded "z" within an earlier "word".

Marcelo Branco

MrExcel MVP
Given the OP's examples, maybe your search should be for " z " (space/z/space) instead of just "z", otherwise you might pick up an embedded "z" within an earlier "word".
Rick,
Maybe you are right.

It would be relevant to know the expected results for
z123 frt abcd
z1234 frt abcd

pto160,
Could you tell us the expected results in the two cases above?
Such cases can occur?

M.

Last edited:

oldbrewer

Board Regular
azaz 4567 z abcd is difficult to handle..... (abcd required)

Marcelo Branco

MrExcel MVP
azaz 4567 z abcd is difficult to handle..... (abcd required)
Searching for " z " as Rick suggested in post #12

M,

pgc01

MrExcel MVP
Hi

Try in B2:

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

pgc01

MrExcel MVP
Or, simpler formulas with a helper column:

<br>

Rick Rothstein

MrExcel MVP
Constructed slightly differently, but apparently along the same lines as PGC's formula in Message #16...

=IFERROR(MID(A1,IFERROR(1+SEARCH(" z "," "&A1&" "),0)+SEARCH(" ???? ",MID(" "&A1&" ",FIND(" z "," "&A1&" z ")+2,99)),4),"")

Last edited:

oldbrewer

Board Regular
what if z 1234 adf z 9999 4532
ie starts with a z

Marcelo Branco

MrExcel MVP
The two formulas (pgc01 and Rick) are much simpler than my formula. I have taken a bad and complicated approach.

M.

Last edited: