Copy only the number from a text string
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
|