VBA find character and extract number

[neveu]

Hello,

I have a list of items in column E that contains information like these

Excel Workbook
	X
4	10T2N30
5	10T3N30
6	14T2N30
7	14TN
8	15T2N30
9	20TN
10	21T2N30
11	21T2N60
12	21T3N30
13	2FM20TN
14	30T0,5N60
15	30T0.5N3FM
16	30T2.5%N31
17	30TN
18	40TN
19	45T2N60
20	45T3N60
21	45TN
22	60T1
23	60T1N75
24	60TN
25	75TN
26	8T2N30
27	90TN
28	BEZ10T3N30
29	BEZ3T3.5N3
30	BEZN
31	Sofort
32	Sofort2%
33	Sofort3%
Open Invoices

Excel 2007



What i would need to do is the folowing,
if the first 3 characters are BEZ than to search for the character "T" in the string and to retrieve the number after it in the corespondign cell on column M. otherwise to put "N"

 

[Maheshp]

if the first 3 characters are BEZ than to search for the character "T" in the string and to retrieve the number after it in the corespondign cell on column M. otherwise to put "N"

To retrive the number after the character "T" try in cell F2

=IF(TRIM(LEFT(E3,3))="BEZ",MID(E3,FIND("T",E3)+1,10),"N")

i didnt get "corespondign cell on column M", what does Column M contain
is it if string contain "BEZ" then vlookup number after T from another column

 

[neveu]

hello Maresh,


thank you very much for your help.
how could i translate this into VBA code?
i would need it to insert in a bigger procedure.

could you please help?

thank you

 

[neveu]

just checked it and for text BEZ10T3N30 it returns 3N30 but i would only need 3.
and for BEZ3T3.5N3 it returns 3.5N3 but i only need 3.5

...

 

[poolhall]

neveu,

try this:

Function MatchNum(Str As String) As String
With CreateObject("VBScript.RegExp")
     .Pattern = "(((-|\+)?\d+\.?\d*)|any)"
     MatchNum = .Execute(Str)(0)
End With

End Function

Sub test35345435()
Dim myRange As Range, c As Range

Set myRange = Sheet1.Range("B2:B31")

For Each c In myRange
    If Left(c.Value, 3) = "BEZ" Then
        On Error Resume Next
        Sheet1.Range("M" & c.Row).Value = _
            MatchNum(Mid(c.Value, InStr(1, c.Value, "T", vbTextCompare), 255))
    End If
Next c
End Sub

I assumed that your worksheet code name is sheet1 and your range is B2:B31, change it if needed.

 

[neveu]

it works perfectly.

thanks a million

 

[poolhall]

you're welcome