Extract 4 character string

pto160

Active Member
Joined
Feb 1, 2009
Messages
303
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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
41,686
Office Version
365
Platform
Windows
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. ;)
 
Last edited:

Marcelo Branco

MrExcel MVP
Joined
Aug 23, 2010
Messages
16,296
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.
 
Last edited:

oldbrewer

Board Regular
Joined
Apr 11, 2010
Messages
10,985
why aren't we searching for batch followed by a space and returning the first 4 characters after that space ?
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
35,395
Office Version
2010
Platform
Windows
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...

Code:
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
 
Last edited:

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
35,395
Office Version
2010
Platform
Windows
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.
 
Last edited:

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
41,686
Office Version
365
Platform
Windows
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

Active Member
Joined
Feb 1, 2009
Messages
303
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

Active Member
Joined
Feb 1, 2009
Messages
303
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?
 

Forum statistics

Threads
1,077,677
Messages
5,335,618
Members
399,029
Latest member
JSO

Some videos you may like

This Week's Hot Topics

Top