# Extract 4 character string

#### pto160

##### Active Member
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?

### Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.

#### Peter_SSs

##### MrExcel MVP, Moderator
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
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.,

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
why aren't we searching for batch followed by a space and returning the first 4 characters after that space ?

#### Rick Rothstein

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

#### Peter_SSs

##### MrExcel MVP, Moderator
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

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

1,102,601
Messages
5,487,795
Members
407,610
Latest member
bellakim00

### This Week's Hot Topics

• Timer in VBA - Stop, Start, Pause and Reset
[CODE=vba][/CODE] Option Explicit Dim CmdStop As Boolean Dim Paused As Boolean Dim Start Dim TimerValue As Date Dim pausedTime As Date Sub...
• how to updates multiple rows in muliselect listbox
Hello everyone. I need help with below code. code is only chaning 1st row in mulitiselect list box. i know issue with code...
• Delete Row from Table
I am trying to delete a row from a table using VBA using a named range to find what I need to delete. My Range is finding the right cell. In the...
• Assigning to a variable
I have a for each block where I want to assign the value in column 5 of the found row to the variable Serv. [CODE=vba] For Each ws In...
• Way to verify information
Hi All, I don't know what to call this formula, and therefore can't search. I have a spreadsheet with information I want to reference...
• Active Cell Address – Inactive Sheet
How to use VBA to get the cell address of the active cell in an inactive worksheet and then place that cell address in a location on the current...