Lookup number in text string

[retboell]

Hi,

I'm searching for a solution to lookup and return the number of a cell/text string. Example:

[TABLE="class: grid, width: 330"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]Input[/TD]
[TD]Return[/TD]
[/TR]
[TR]
[TD]asd-1234567890123axbf[/TD]
[TD="align: right"]1234567890123[/TD]
[/TR]
[TR]
[TD]1234567890123_00sajs1[/TD]
[TD="align: right"]1234567890123[/TD]
[/TR]
[TR]
[TD]0-1234567890123sdfjquwe[/TD]
[TD="align: right"]1234567890123[/TD]
[/TR]
[TR]
[TD]asdasd-1-n1234567890123[/TD]
[TD="align: right"]1234567890123[/TD]
[/TR]
</tbody>[/TABLE]

The number will always start with 12345 (but the rest varies) and will always be 13 digits. Otherwise it can be mixed with other numbers and text in the cell (see example)

Help is much appreciated!

 

[philwojo]

I think this should work, there might be better ways to do this though.

Try this:

=MID(A1,FIND(12345,A1),13)

Phil

 

[rlv01]

You are skipping some numbers while including others. What are the rules about that.?For example. what if the input string was:

[table="width: 500"]
[tr]
[td]asdasd-q98765321sdf-n1234567890123rtyg[/td]
[/tr]
[/table]

What should the output be?

 

[GR00007]

Here's a function that will return just numbers:

Function NumbersOnly(r As Range) As String
vOut = ""
vStr = r.Formula
For i = 1 To Len(vStr)
  If Mid(vStr, i, 1) >= "0" And Mid(vStr, i, 1) <= "9" Then
    vOut = vOut & Mid(vStr, i, 1)
  End If
Next
NumbersOnly = vOut
End Function

 

[retboell]

The output for this would be 1234567890123 since the number I'm looking for is always 13 characters and always starts with 12345.

 

[jtakw]

Hi,

You already have a formula solution in Post # 2.

 

[lezawang]

Hi
Can I ask you what does line 3

 r.Formula

do? you did not assign any formula? can you please break it down?
Thank you.

Here's a function that will return just numbers:

Function NumbersOnly(r As Range) As String
vOut = ""
vStr = r.Formula
For i = 1 To Len(vStr)
  If Mid(vStr, i, 1) >= "0" And Mid(vStr, i, 1) <= "9" Then
    vOut = vOut & Mid(vStr, i, 1)
  End If
Next
NumbersOnly = vOut
End Function

 

[Michael M]

@lezawang
It's not really needed !!
You could use

vStr = r

OR

vStr = r.value

OR

vStr = r.formula

 

[lezawang]

Thank you very much for the help Michael M. I have another question please
Why his function is taking a range as a argument. Should be be a String?

 Function NumbersOnly(r as String) As String 

Thank you once again.

 

[jtakw]

You might want to start your own thread if you have many questions, rather than "hi-jacking" someone else's thread...