# Thread: picking up code from text

1. ## picking up code from text

Hi

I am trying to pick up a number from a length of text in a bank account description field.

The number 8 in the instance below identifies it as a particular type of transaction, wheras all the other numbers between 0 and 9 that appear as the first characther relate to other transaction types (see example below).

I can't use left, right, mid etc because the length of peoples names will be inconsistent. Is there some other way of isolating the first number in a string ?

example:
Fred Bloggs 81668 STOFrom: Xx-xx-xx xxxxxxxx

cheers

2. You could use a UDF to "split" the data into fields and then work on just one field.
(I don't have Excel to scratch one up for you, but someone will have a suggestion.)

cheers, Paul

3. Is there only 1 multi-digit number after the name?

One array formula (CTRL+Shift+Enter): =--MID(A1,MATCH(TRUE,ISNUMBER(1*MID(A1,ROW(\$1:\$101),1 )),0),COUNT(1*MID(A1,ROW(\$1:\$101),1)))

I threw this one together which is not an array formula. It assumes that there is only 1 space between the first and last name and 1 space after the number.

=--MID(A1,FIND(CHAR(7),SUBSTITUTE(A1," ",CHAR(7),2)),FIND(CHAR(7),SUBSTITUTE(A1," ",CHAR(7),3))-FIND(CHAR(7),SUBSTITUTE(A1," ",CHAR(7),2)))

4. Robert,

Here's a User Defined Function that will do the trick with a variety of input:

Code:
```Option Explicit

Function GetTransCode(zTRString As String) As Integer

Dim vParts As Variant
Dim iCntr  As Integer

GetTransCode = 0
vParts = Split(zTRString, " ")                  '*** Break into pieces on space            ***

For iCntr = 0 To UBound(vParts) - 1             '*** Loop through zero based variant array ***
If (Val(vParts(iCntr)) > 0) Then             '*** Convert to value and check non zero   ***
GetTransCode = Val(Left(vParts(iCntr), 1)) '*** Strip off first character & Return    ***
Exit For                                   '*** Found first number Exit               ***
End If
Next iCntr

End Function 'GetTransCode```
Sample:
TransCode.PNG