sort/extract strings with similar format

  • Thread starter Thread starter Victorv
  • Start date Start date
V

Victorv

I have a download of bank statements. The description column contains
firms reference number which is in the following format
1numeric,1alpha, 5numeric eg. 9z99999, 1f12345.
The problem is that the description column would read:
deposit cape town 9z99999
CAMS payment 1f12345
5j54321
Payment form john D 3a98765

Is there any way I can sort or extract the firm number to a seperat
column if it is not always in the same place in every row? They are i
the same column!

I would really appreciate it if anyone could help me solve this

+----------------------------------------------------------------
| Attachment filename: eipf 01 oct- 07 nov 2003.xls sample.xls
|Download attachment: http://www.excelforum.com/attachment.php?postid=355057
+----------------------------------------------------------------
 
Looks like you are in luck. The number you require is always on th
right (?)

So a formula like : =RIGHT(A1,7
 
Let's have another go then. Copy/paste this formula to a VB Editor macr
module (use keyboard Alt + F11) and put a formula in your workshee
like :-
=GetRef(A1).

This finds the first number in the cell value and assumes that it i
the start of the reference you want.

'------------------------------------------
Public Function GetRef(str As String)
GetRef = "#N/A"
For c = 1 To Len(str)
If IsNumeric(Mid(str, c, 1)) Then
GetRef = Mid(str, c, 7)
Exit For
End If
Next
End Function
'----------------------------------------
 
Back
Top