Extract string starting with ... from a cell

[wovawefo]

Hi!

I'm trying to write a macro to extract a certain text from a cell. The string in the cell is the following usually: VIFIR - kód :k200010006……. What I need is to extract the 10 digit code from it. but it does not always start at the 14th character and not always with a k. Can start with "k", "e", "f", and "a". Dunno if it is possible to find the second "k" or first "e", "f" or "a" in the cell's text, store the positions of it (13th character). Is it? Cause if I could do that, than i might be able to figure out how to extract 10 characters starting from the stored one.

I searched a lot and havent found an answer.

Thanks for the help.

Peter

 

[sanrv1f]

Hello and welcome to the board,

Would it always be a alphabet followed by nine numeric?

could you post some more sample data?

 

[SteveO59L]

Is it always the 10 characters following the ":"?

 

[wovawefo]

it is always one letter, than 9 numbers

k200210010, a200220002, f110210001

it always starts with either a "k", an "f", and "a", or an "e"

it does not always start right after ":"

the problem is sometimes they start right after it, sometimes with one space after, sometimes two. and the other thing, the code is not always the last 10 chars of the string.

sweet huh?

thanks

 

[barry houdini]

If the first digit of the 10 charcater code is always the first digit of the string then you could extract with this formula

=MID(A1,MIN(FIND({1,2,3,4,5,6,7,8,9,0},A1&1234567890))-1,10)

 

[Krishnakumar]

Hi,

May be..

=MID(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"0123456789")-1),10)

 

[mikerickson]

If the number is the last 9 digit number in the string, the CSE formula
=MID(A1, MAX(IF(ISNUMBER(MID(A1,ROW(INDIRECT("1:100")),9)+0), ROW(INDIRECT("1:100")))) - 1, 10)

will work.
This needs to be confirmed with Ctrl-Shift-Enter (Cmd+Return for Mac)

 

[wovawefo]

some examples:

VIFIR - kód :k200010006…….
VIFIR - kód : k200010003..
VIFIR - kód : a100010043

 

[mikerickson]

All the posted formula will extract the correct string from those examples.

Here's another CSE formula, this one tests for the leading letter in addition to the other criteria.

=MID(A1,MAX(IF(ISNUMBER(FIND(MID($A$1,ROW(INDIRECT("1:100")),1),"kafe")+MID(A1,ROW(INDIRECT("2:101")),9)),ROW(INDIRECT("1:100")),)),10)

If these doen't work could you post some examples of where they fail.

 

[sanrv1f]

another

Function xtract(ByVal ref As String) As String
With CreateObject("VBScript.Regexp")
    .Pattern = "[a-z]\d{9}"
    If .test(ref) Then xtract = .Execute(ref)(0)
End With
End Function

Excel Workbook
	A	B
1	VIFIR - kd :k200010006	k200010006
2	VIFIR - kd : k200010003	k200010003
3	VIFIR - kd : a100010043	a100010043
Sheet1

Excel 2003

Cell Formulas
Range	Formula
B1	=xtract(A1)