Solved

# extract string from column

Posted on 2012-03-26
348 Views
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.
0
Question by:BauwensER

LVL 10

Accepted Solution

Dear,

Something like this should work :

``````text = "A0000AXYZTI00"

long_text = Len(text)

pos_t = Instr (1, text , "T")
pos_2nd_a = Instr (2, text , "A")

value = Mid("A0000AXYZTI00", pos_2nd_a+1, pos_t-pos_2nd_a)
``````
0

LVL 39

Expert Comment

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

Author Comment

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

LVL 29

Expert Comment

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"
``````
0

LVL 10

Expert Comment

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

LVL 29

Expert Comment

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
``````
Assume

A1 = "A0000AXYZTI00"

and

A2 = "A0000A12345TI00"

Enter
``````=Splitter(A1)
``````
into B1 and copy it down to B2.
0

## Featured Post

In Debugging – Part 1, you learned the basics of the debugging process. You learned how to avoid bugs, as well as how to utilize the Immediate window in the debugging process. This article takes things to the next level by showing you how you can us…
Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.