asked on

extract string from column

I have the following situation:

I have a column that is contains the following data (string):

A0000AXYZTI00
A0000A12345TI00
etc..

I need to obtain the string value that starts with the 2nd 'A' and ends before the 'T'
So in this case it would be AXYX abd A12345. Sometimes the length between the 2nd A and the T varies. Is there an easy way of doing this with some sort of string function?

Many thanks.

ASKER CERTIFIED SOLUTION

Tobias

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Pratima

in query
Select Mid("A0000AXYZTI00", Instr (2, text , "A") +1 ,Instr (1, text , "TI00") )

BauwensER

ASKER

I just tried value: Mid([MFAnumber], Instr (2, [MFAnumber], "A") +1 ,Instr (1, [MFAnumber], "TI00") ) but it does not return the right values.

Badotz

Something like this, perhaps:

Function Splitter(str)
    Dim ra
    Dim rb

    ra = Split(str, "T") '' "T" segment(s)
    rb = Split(ra(0), "A") '' "A" segment(s)

    Splitter = "A" & rb(2) '' Return value
End Function

MsgBox Splitter("A0000AXYZTI00") '' "AXYZ"
MsgBox Splitter("A0000A12345TI00") '' "A12345"

Open in new window

Tobias

Select Mid([MFAnumber], Instr(2, [MFAnumber], "A")+1 ,Instr(1,[MFAnumber], "T")- Instr(2, [MFAnumber], "A")-1)

Badotz

You want concise? You get concise!

Put this in a module:

Function Splitter(ByVal str As String) As String
    Splitter = "A" & Split(Split(str, "T")(0), "A")(2)
End Function

Open in new window

Assume

A1 = "A0000AXYZTI00"

and

A2 = "A0000A12345TI00"

Enter

=Splitter(A1)

Open in new window

into B1 and copy it down to B2.