Announcement

Collapse
No announcement yet.

Unconfigured Ad Widget

Collapse

Extract Digits From Text

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Extract Digits From Text

    This is my first post, so please be gentle with me!

    I have a string in a cell that contains a name, a 3-4 character alpanumeric code and then a 4-6 digit number, each separated by a space. An example or two:

    J Bloggs SPEC 123456
    J Smith AG06 9364
    J & K Brown SWP 358686

    I need to be able to have in a new cell the 4-6 digit number at the right of the cell.

    Could someone please give a formula that will separate the data I need? I've tried numerous ways without success.

    Many thanks!

    Buff

  • #2
    Re: Extracting Text From A String

    Welcome Buff Daddy!

    To do this accurately, you need to be able to access the VBA.StrReverse function. You can only access the VBA.StrReverse function with a user defined function:

    Code:
    Public Function lastchunk(ByRef s As String)
    lastchunk = Right(s, InStr(1, VBA.StrReverse(s), " ") - 1)
    End Function
    Put this in a new module for the workbook that you are working on, and then on the worksheet, in the column next to the one that you want to extract the "last chunk" from, type:
    =lastchunk(-reference the cell in here-)

    Regards,

    G.

    Comment


    • #3
      Re: Extracting Text From A String

      See Extract Number From Text

      Or, use

      =IF(ISNUMBER(--RIGHT(A1,6)),--RIGHT(A1,6),IF(ISNUMBER(--RIGHT(A1,5)),--RIGHT(A1,5),--RIGHT(A1,4)))

      Comment


      • #4
        Re: Extract Digits From Text

        Thanks for the replies. I've gone with Dave's solution. Thanks to you both.

        Buff

        Comment


        • #5
          Re: Extract Digits From Text

          Hi,

          =MID(A1,LOOKUP(9.999999999E+307,FIND(" ",A1,ROW(INDEX(A:A,1):INDEX(A:A,LEN(A1)))))+1,255)

          HTH
          Kris

          ExcelFox

          Comment

          Trending

          Collapse

          There are no results that meet this criteria.

          Working...
          X