Results 1 to 2 of 2

Thread: Pulling an unknown number of spaces out of a Excel cell

  1. #1

    Thread Starter
    Junior Member
    Join Date
    Apr 2006
    Posts
    22

    Pulling an unknown number of spaces out of a Excel cell

    The contents of an Excel cell are given below:

    P12001 Revenue from Prod. Handling Agreements/Tariffs

    The number of digits within the account code on the left changes.

    I want some code that will go through all the cells and extract the account number.

    I cannot set an arbitarily high value just incase it starts pulling the name ie 'Revenue....' into the string.

    I know it will be something like:

    Left(Cells(y, x).Value, 'how do I set this value') = Accountnumber

    There is always a space after the account number. Could I use this fact?

  2. #2
    Frenzied Member DKenny's Avatar
    Join Date
    Sep 2005
    Location
    on the good ship oblivion..
    Posts
    1,171

    Re: Pulling an unknown number of spaces out of a Excel cell

    Use the Instr function to find the first occurance of a space and set the length to that minus one.
    VB Code:
    1. Left(Cells(y, x).Value, InStr(1,Cells(y, x).Value, " ")-1)
    Declan

    Don't forget to mark your Thread as resolved.
    Take a moment to rate posts that you think are helpful

Posting Permissions

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



Click Here to Expand Forum to Full Width