Pulling an unknown number of spaces out of a Excel cell

[djtaverner]

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?

[DKenny]

Use the Instr function to find the first occurance of a space and set the length to that minus one.

Left(Cells(y, x).Value, InStr(1,Cells(y, x).Value, " ")-1)