extract numbers

[olegvolf]

Hello

I am a little stuck.
in cell A1 i have "32 hello 57" the numbers are changing.
How can i extract numbers before the word "hello" and after.

Thank you very much

[Sixthsense..]

If hello is constant then just replace it by using substitute() or replace() functions :)

[mancubus]

below formula will return 3257


=SUMPRODUCT(MID(0&A1,LARGE(INDEX(ISNUMBER(--MID(A1,ROW($1:$25),1))*ROW($1:$25),0),ROW($1:$25))+1,1)*10^ROW($1:$25)/10)

[Sixthsense..]

Function GetText(v As Variant) As String
Dim sRes As String, i As Long

For i = 1 To Len(v)
If Mid(v, i, 1) Like "[0-9]" Then sRes = sRes & Mid(v, i, 1)
Next i

GetText = sRes

End Function

In A1 Cell

32 hello 57

In B1 Cell

=GetText(A1)

[olegvolf]

Thank you very much for the quick replay
i am not succeeding to get it to work.

[Kenneth Hobs]

More sample data is needed for us to see the pattern for the logic or explain all the cases. Are all the possible values always the first and last numbers? Are they always delimited into 3 fields separated by space character?

You were provided with two solutions that both returned 3257. You need to explain what result you expected.

This routine can be used to get any field in a delimited string.

'=Field(A1, 2)
Function Field(cell, pos As Integer, Optional delimit As String = " ")
Dim a() As String
a() = Split(cell, delimit)
Field = a(pos - 1)
End Function

[olegvolf]

More sample data is needed for us to see the pattern for the logic or explain all the cases. Are all the possible values always the first and last numbers? Are they always delimited into 3 fields separated by space character?

You were provided with two solutions that both returned 3257. You need to explain what result you expected.

This routine can be used to get any field in a delimited string.

'=Field(A1, 2)
Function Field(cell, pos As Integer, Optional delimit As String = " ")
Dim a() As String
a() = Split(cell, delimit)
Field = a(pos - 1)
End Function

Thank you
I am very sorry.
I should explain better
I seek a code to extract 2 values into a variables from the expression one variable equals 32 and second 57.
the word 'hello' is always the same
before and after the word always one space.
the value always change.
The can be a round value like in example but then can bealso like this
3.432 hello 5.986
I hope now is more clear.
Sorry and thanks

Oleg

[Kenneth Hobs]

You could use my Field() UDF for fields 1 and 3.
Or do it like this in a routine if you want more control. This would need more work depending on your data.

Sub ken()
Dim s() As String, sv As String, n1 As Double, n2 As Double
sv = Range("A1").Value2
s() = Split(sv, " ")
If IsNumeric(s(0)) Then n1 = s(0)
If IsNumeric(s(UBound(s))) Then n2 = s(UBound(s))
MsgBox Join(Array(sv, n1, n2), vbLf)
End Sub

Likely, others can come up with formulas to do this as well if your data is always in that format.

[olegvolf]

Hello
thank you.
Please it not to hard to explain this great solution.
I thought the word 'hello' should be used.
please

[Kenneth Hobs]

sv is assigned the string value in cell A1 of the activeworksheet. You can easily test others cells or assign a string directly. The method uses Split() to split by the space character. The first element of the array index is 0 or lbound(s) in this case. The last element is 2 since or ubound(s). A string array is always 0 based and you would have 3 element "field" values.

[SamT]

Hello
thank you.
Please it not to hard to explain this great solution.
I thought the word 'hello' should be used.
please
It uses the space after the first number and the space before the last number.

Truthfully, it uses all the spaces but the LBound and Ubound limit it to the first and last spaces.

This would work

32 this is a test 57
n1 = 32, n2 = 57


this is a test 57
n1 = 0, n2 = 57


this is a test
n1 = 0 n2 = 0


32.01 57.88
n1 = 32.01 n2 = 57.88


32.01 19.99 57.88
n1 = 32.01 n2 = 57.88