Extract 4 character string

[pto160]

I'm trying to extract a 4 character string that can be numbers or a text string. Here is the example.

Sheet1

	A	B
	Result
abc rtyidf 1034 567
fg abcd 789 1464654	abcd
erd f rtyu	rtyu

<colgroup> <col style="WIDTH: 30px; FONT-WEIGHT: bold"> <col style="WIDTH: 126px"> <col style="WIDTH: 64px"></colgroup> <tbody>
[TD="bgcolor: #cacaca, align: center"]1[/TD]

[TD="bgcolor: #cacaca, align: center"]2[/TD]

[TD="align: right"]1034[/TD]

[TD="bgcolor: #cacaca, align: center"]3[/TD]

[TD="bgcolor: #cacaca, align: center"]4[/TD]

</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

How do you do it?

 

[mikerickson]

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]

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


[TABLE="class: grid"]
<tbody>[TR]
[TD="bgcolor: #DCE6F1"][/TD]
[TD="bgcolor: #DCE6F1"]

A​

[/TD]
[TD="bgcolor: #DCE6F1"]

B​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

1​

[/TD]
[TD]

Text​

[/TD]
[TD]

Result​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

2​

[/TD]
[TD]

abc rtyidf 1034 567​

[/TD]
[TD]

1034​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

3​

[/TD]
[TD]

fg abcd 789 1464654​

[/TD]
[TD]

abcd​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

4​

[/TD]
[TD]

erd f rtyu​

[/TD]
[TD]

rtyu​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

5​

[/TD]
[TD]

vgthn 1234 1234​

[/TD]
[TD="bgcolor: #FFFF00"]

1234​

[/TD]
[/TR]
</tbody>[/TABLE]


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]

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[TABLE="width: 167"]
<tbody>[TR]
[TD="class: xl63, width: 167"]Result:
7896

[/TD]
[/TR]
</tbody>[/TABLE]

 

[Marcelo Branco]

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[TABLE="width: 167"]
<tbody>[TR]
[TD="class: xl63, width: 167"]Result:
7896
[/TD]
[/TR]
</tbody>[/TABLE]

We need more examples. Could you provide?

M.

 

[pto160]

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

	A	B
	Result z character
abcd z dfrg rtyu fghj	dfrg
1234 7834 z 1456 7364
z erdf rtyu	erdf

<colgroup> <col style="WIDTH: 30px; FONT-WEIGHT: bold"> <col style="WIDTH: 144px"> <col style="WIDTH: 114px"></colgroup> <tbody>
[TD="bgcolor: #cacaca, align: center"]1[/TD]

[TD="bgcolor: #cacaca, align: center"]2[/TD]

[TD="bgcolor: #cacaca, align: center"]3[/TD]

[TD="align: right"]1456[/TD]

[TD="bgcolor: #cacaca, align: center"]4[/TD]

</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

 

[mikerickson]

The formula =MID(A1,FIND("z ",A1&"z ")+2,4) will do that

 

[AlKey]

Try this

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

Unknown
	A	B
1		Result z character
2	abcd z dfrg rtyu fghj	dfrg
3	1234 7834 z 1456 7364	1456
4	z erdf rtyu	erdf
Sheet2

Cell Formulas
Range	Formula
B2	=MID(A2,SEARCH("z",A2)+2,4)
B3	=MID(A3,SEARCH("z",A3)+2,4)
B4	=MID(A4,SEARCH("z",A4)+2,4)

 

[pto160]

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
	Result z character
abcd z dfrgd rtyu fghj	rtyu
1234 7834 z 1456d 7364
z erdf rtyu	erdf

<colgroup> <col style="WIDTH: 30px; FONT-WEIGHT: bold"> <col style="WIDTH: 152px"> <col style="WIDTH: 114px"></colgroup> <tbody>
[TD="bgcolor: #cacaca, align: center"]1[/TD]

[TD="bgcolor: #cacaca, align: center"]2[/TD]

[TD="bgcolor: #cacaca, align: center"]3[/TD]

[TD="align: right"]7364[/TD]

[TD="bgcolor: #cacaca, align: center"]4[/TD]

</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

 

[Marcelo Branco]

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


[TABLE="class: grid"]
<tbody>[TR]
[TD="bgcolor: #DCE6F1"][/TD]
[TD="bgcolor: #DCE6F1"]

A​

[/TD]
[TD="bgcolor: #DCE6F1"]

B​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

1​

[/TD]
[TD]

Text​

[/TD]
[TD]

Result​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

2​

[/TD]
[TD]

abc rtyidf 1034 567​

[/TD]
[TD]

1034​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

3​

[/TD]
[TD]

fg abcd 789 1464654​

[/TD]
[TD]

abcd​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

4​

[/TD]
[TD]

erd f rtyu​

[/TD]
[TD]

rtyu​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

5​

[/TD]
[TD]

vgthn 1234 1234​

[/TD]
[TD="bgcolor: #FFFF00"]

1234​

[/TD]
[/TR]
</tbody>[/TABLE]


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.