Hi Kaj,

One way is to use a User-Defined Function (UDF). Here's some sample code:

Public Function GetNumberFromEnd(rng As Range) As Variant
Dim nSpacePos As Integer
Dim sNumber As String

nSpacePos = InStrRev(rng.Value, " ")
If nSpacePos Then
'/ found space, look for number after it
sNumber = Mid$(rng.Value, nSpacePos + 1)
If IsNumeric(sNumber) Then
GetNumberFromEnd = Val(sNumber)
Else
GetNumberFromEnd = ""
End If
Else
GetNumberFromEnd = ""
End If
End Function

To use it, just enter =GetNumberFromEnd(A1) (replace A1 with whatever cell
you want to get the number from) in a worksheet cell.


Regards,

Jake Marx
MS MVP - Excel


"Kaj Pedersen" wrote in message
...
Hi,
When a cell contains text as well as a number is it possible then to copy
only the number to a new cell?

The number does not appear on a certain position from the beginning of the
cell as the text varies, but it is always to be found at the end of the
string and vary between 3 and 5 digits. Before the number is always a
space.

There are a different number of spaces in the text also.



Regards

Kaj Pedersen