formula needed

[boumbo]

i have these in a column. each line is a row

128982 VC AAAA
1389883; MC AAAA
1589 DC BBBB
179024;VC DDDDD
1212313;AM EEEEE
18902 MC FFFFF
18902 FFFFF
3033383; B#111; VC; XXXX
3035383; B#222; DC; YYYY

i want the next column to be

VC
MC
DC
VC
AM
MC
<blank>
VC
DC

any formula i can put in a cell to generate the next column?

 

[DCrake]

Will the item you want to parse only contain 2 alpha characters?
Will any other item ever contain 2 alpha characters

If the answers are Yes and No then not to dificult to do, otherwise it could get complicated.

 

[boumbo]

Yes
No

so any solution?

Will the item you want to parse only contain 2 alpha characters?
Will any other item ever contain 2 alpha characters

If the answers are Yes and No then not to dificult to do, otherwise it could get complicated.

 

[Access_guy49]

Based on looking at the data... nothing is consistent, Number of characters (before and after) There are no consistent delimiters...
I don't see how a single formula can be used without being extremly complex...
I can't wait to see what one of the guru's come up with

 

[NBVC]

Is it possible for you to create a list on the side of the possible codes that would be included?

If so, assuming you entered in G1:G4, say

DC
VC
AM
MC


then, assuming your data is in column A, starting at A1, then in B1 enter formula:

=IF(ISNA(LOOKUP(9.99999E+307,SEARCH(" "&$G$1:$G$4&" ",SUBSTITUTE($A1,";"," ")),$G$1:$G$4)),"",LOOKUP(9.99999E+307,SEARCH(" "&$G$1:$G$4&" ",SUBSTITUTE($A1,";"," ")),$G$1:$G$4))

copied down

or if you are using Excel 2007 or later:

=IFERROR(LOOKUP(9.99999E+307,SEARCH(" "&$G$1:$G$4&" ",SUBSTITUTE($A1,";"," ")),$G$1:$G$4),"")

Note: I added more robustness to the formula so that it does not capture the 2 letter codes within longer codes.. e.g. it won't return VC if you have string FFFVCDDFD. It will only return VC if it is standalone with space or semicolon on either side.

 

[DCrake]

Sample Data
128982 VC AAAA
1389883; MC AAAA
1589 DC BBBB
179024;VC DDDDD
1212313;AM EEEEE
18902 MC FFFFF
18902 FFFFF
3033383; B#111; VC; XXXX
3035383; B#222; DC; YYYY

Public Function SpecialParse(AnyString As string) As string

'/Task 1 : Replace all spaces with semi-colons

AnyString = replace(anystring," ",";")

'/Task 2 : Split the data elements into an array

Dim Components

Components = Split(AnyString,";")

'/Task 3 : Enumerate throught the elements to find a 2 digit characters string

For x = 0 To Components(UBound)

    '/Test for 2 alpha characters
    If Len(Components(x)) = 2 then
       If IsNumeric(Left(Component(x),1)) = False And IsNumeric(Right(Component(x),1)) = False Then
            SpecialParse = Components(x)
            Exit Function
       End If
    End If

Next

'/If no Component matches above test then return "--"
SpecialParse = "--"

End Function

Add this function to a standard module and call as follows

=SpecialParse(C2)

Where C2 = is the cell that contains the full string

If used in Access you would use a query

SubSet:SpecialParse([FullStringFieldName])

Code is for brevity only and is untested, but the logic is correct.