Copy only the number from a text string

[Kaj Pedersen]

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 Marx]

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

 

[Trevor Shuttleworth]

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

 

[Trevor Shuttleworth]

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]

Hi Trevor,

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

 

[Tom Ogilvy]

=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.

 

[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