Results 1 to 4 of 4
  1. #1
    New Lounger
    Join Date
    Jun 2016
    Posts
    5
    Thanks
    3
    Thanked 0 Times in 0 Posts

    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
    Last edited by Robert Street; 2016-06-30 at 09:08. Reason: mistake

  2. #2
    WS Lounge VIP
    Join Date
    Dec 2009
    Location
    Earth
    Posts
    8,164
    Thanks
    47
    Thanked 976 Times in 906 Posts
    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. #3
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,468
    Thanks
    30
    Thanked 61 Times in 57 Posts
    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)))
    Last edited by kweaver; 2016-06-30 at 11:33.

  4. #4
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,433
    Thanks
    371
    Thanked 1,456 Times in 1,325 Posts
    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
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •