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
1		Result
2	abc rtyidf 1034 567	1034
3	fg abcd 789 1464654	abcd
4	erd f rtyu	rtyu

<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?

 

[pto160]

Thanks for the explanation. So really there is no formula solution.
With the UDF can you use to select different length characters. So instead of 4, you can select 5?

 

[Peter_SSs]

Thanks for the explanation. So really there is no formula solution.
With the UDF can you use to select different length characters. So instead of 4, you can select 5?

Could you post 8-10 samples of your actual data (with anything sensitive disguised) and showing any variations with the format of that data, and also the expected result for each of those samples?

As can be seen from reading this thread, it is very difficult to devise a good general solution based on one sample at a time, especially when that sample isn't what you actually have.

 

[Marcelo Branco]

I am afraid the formulae in post #16, 17 and 18 from the string

„abcd z dfrgd r yu fghj” or „abcd z dfrgd ry u fghj”

extracts „r yu” or „ry u”, resp.,

instead of fghj.

My formula in post #11 works

	A​	B​	C​
1​	Text​	Helper​	Result​
2​	abcd z dfrgd r yu fghj​	z dfrgd r yu fghj​	fghj​
3​	abcd z dfrgd r yu fghj​	z dfrgd r yu fghj​	fghj​

<tbody>
</tbody>

Formula in B2 (helper)
=IFERROR(MID(A2,SEARCH(" z ",A2)+1,200),A2)

Array formula in C2
=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.

 

[oldbrewer]

why aren't we searching for batch followed by a space and returning the first 4 characters after that space ?

 

[Rick Rothstein]

Thanks for the explanation. So really there is no formula solution.
With the UDF can you use to select different length characters. So instead of 4, you can select 5?

I have modified my original UDF so that you can specify how many characters to return. In order to do that, I had to add an additional argument (the number of characters to return), and I changed the name of the function to Chars (note the "s" on the end to distinguish it from Excel's CHAR function), plus I also changed it to use " Batch " instead of " z ", so you would now have to write your formula like this....

For 4 characters: =Chars(A2,4)

For 5 characters: =Chars(A2,5)

and so on. Here is the revised macro to use...

Function Chars(S As String, Num As Long) As String
  Dim X As Long, Txt() As String
  Txt = Split(Split(" " & S, " Batch ", , vbTextCompare)(1))
  For X = 0 To UBound(Txt)
    If Txt(X) Like Application.Rept("[A-Za-z0-9]", Num) Then
      Chars = Txt(X)
      Exit Function
    End If
  Next
End Function

 

[Peter_SSs]

why aren't we searching for batch followed by a space and returning the first 4 characters after that space ?

Because of post #21 & #22

 

[Rick Rothstein]

Because of post #21 & #22

Okay, I somehow missed Message #22 (the "z" has no spaces around it, plus it is not the word "Batch")... so now I am confused at what the OP actually wants. I am going to sleep now, so hopefully the OP's needs will be clarified by the time I get up.

 

[Peter_SSs]

plus it is not the word "Batch")... so now I am confused at what the OP actually wants.

My understanding is to read "Batch" in place of "z" in the sample. The point I was making to oldbrewer though was that the expected result is not the first 4 characters after the first space after the "z/Batch".

The general confusion is why I made post #32.

 

[pto160]

I was just trying to provide a real life example using batch instead of z. What I'm trying to do with this formula is to get a 4 digit check number that comes after batch. I think the formulas and macros from Marcelo and Rick give this to me.

 

[pto160]

I tried these formulas and UDF at work and they work great. Marcelo, the formula works excellent. It is pulling the correct text string even with embedded spaces.

Rick, I also used your UDF and it works very good.

Is there a way to change it so it extracts the character string without looking for batch as the starting point?