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

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

Kaj

you could just use a formula:

=VALUE(MID(A1,FIND(" ",A1)+1,LEN(A1)-FIND(" ",A1)))

assuming the data is in cell A1

Regards

Trevor


"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

Jake

fair comment. I had considered multiple spaces before the number itself and
it will work for that. However, additional spaces in the text would result
in #VALUE

Let's try this:

=VALUE(MID(RIGHT(A3,6),FIND(" ",RIGHT(A3,6))+1,LEN(RIGHT(A3,6))-FIND("
",RIGHT(A3,6))))

It only works because the OP said there were 3 to 5 digits after the space.

It copes with "te xt 321", "te x t 321" and "te xt ab 12345"
so I think it will work for everything.

Regards

Trevor


"Jake Marx" wrote in message
...
Hi Trevor,

"Trevor Shuttleworth" wrote in message
...
you could just use a formula:

=VALUE(MID(A1,FIND(" ",A1)+1,LEN(A1)-FIND(" ",A1)))

But this won't work if there are space(s) in the text before the final
space. I gave up (albeit fairly quickly) trying to come up with a simple
function to do this.

Regards,

Jake Marx
MS MVP - Excel

Hi Trevor,

"Trevor Shuttleworth" wrote in message
...
Let's try this:

=VALUE(MID(RIGHT(A3,6),FIND(" ",RIGHT(A3,6))+1,LEN(RIGHT(A3,6))-FIND("
",RIGHT(A3,6))))

That one doesn't work for me with a value like "a a 233" or similar. It
seems that it won't work for anything with 2 separate spaces in the last 6
digits.

Regards,

Jake Marx
MS MVP - Excel

=MAX(IF(ISNUMBER(RIGHT(A1,{1,2,3,4,5})*1),RIGHT(A1 ,{1,2,3,4,5})*1))
Entered with Ctrl+Shift+Enter rather than just enter (since this is an array
formula) should work.

if they are all at least 3 digits you could shorten it to

=MAX(IF(ISNUMBER(RIGHT(A1,{3,4,5})*1),RIGHT(A1,{3, 4,5})*1))

You could probably get more "exciting" formulas posting this in
Worksheet.Functions.
--
Regards,
Tom Ogilvy


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

Hi,
Thank you to both of you for your inputs.
For my purpose both af them worked, but Jake is right when arguing, that
there is a problem with your's Trevor, if you have words with only one
letter.

Regards
Kaj Pedersen