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

 

[wovawefo]

Thank you very much. Works fine . I started to use the function instead of VBA (I have 2007 and does not seem to work but I'll figure out why, cause I'd learn some). Took a while to figure out how arrays work, but read some and it works now. Trying to figure out how it works. Still do not understand all of it, but I'll continue reading on the net and think and I'll be find. But if you could explain briefly, would also help a lot.

Thanks for the many comments on my thread.

p

 

[wovawefo]

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)

hi sanrv1f!

could I ask you to give a small explanation to the function u suggested using?
I have excel 2007 and it does not work. It has a NAME error. I tried to understand
your code, by searching the web, but did not succeed. It would be a GREAT help if
you could give me some heads up on what each command does. I would be really
really greatful. Thank you.
Code:

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</pre>

 

[sanrv1f]

First thing, you need to place the code in a standard module

Goto VBE (Alt + F11) -> Insert -> Module -> Paste the code


As for the code, the Regular expressions (Reg exp) is a pattern matching tool to identify set of chars in a string,

here [a-z] means any small case alphabet, '\d' means any single digit (0-9), the {9} denotes the occurences of the token (any digit),

when you interpret in simple english, Any small case letter followed by 9 numeric chars

see this for more info on Reg Exp

http://www.aivosto.com/vbtips/regex.html

 

[Peter_SSs]

Maybe this?

Excel Workbook
	A	B
1	VIFIR - kd :k200010006 ABC.	k200010006
2	VIFIR - kd : k200010003	k200010003
3	VIFIR - kd : a100010043XY QW	a100010043
Extract Code

 

[wovawefo]

thank you

it works fine!