Copy only the number from a text string

  • Thread starter Thread starter Kaj Pedersen
  • Start date Start date
K

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

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
 
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
 
Hi Trevor,

Trevor Shuttleworth said:
Let's try this:

=VALUE(MID(RIGHT(A3,6),FIND(" ",RIGHT(A3,6))+1,LEN(RIGHT(A3,6))-FIND("
",RIGHT(A3,6))))
Click to expand...

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.
 
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
 
Back
Top