Return string between two spaces

[Knut]

I have a string like this in a cell: AAAA T52650 BBBB.

Need a function to return T52650.

AAAA, T52650 and BBBB can be of variable length, but there will always be a
space between the three.

Anyone???

 

[Don Guillett]

Does this help?

Sub betweenspaces()
With ActiveCell
p1 = InStr(.Value, " ")
'MsgBox p1
p2 = InStrRev(.Value, " ")
'MsgBox p2
MsgBox Mid(.Value, p1, p2 - p1)
End With
End Sub

 

[Ron Rosenfeld]

I have a string like this in a cell: AAAA T52650 BBBB.

Need a function to return T52650.

AAAA, T52650 and BBBB can be of variable length, but there will always be a
space between the three.

Anyone???

Perhaps:

=TRIM(MID(SUBSTITUTE(TRIM(A1)," ",REPT(" ",99)),99,99))

--ron

 

[Knut]

Yes, as a Sub it is okay, but I need a function.
The string will be imported from another source, and no, I cant use text to
columns.
There will be approx up to 100 such strings in column A.

knut

 

[Knut]

That may do the trick, just have to find out what MID, TRIM and SUBSTITUTE
are in Norwegian.


knut

 

[Peo Sjoblom]

Must be similar to Swedish so I just translated the Swedish to Norwegian

=TRIMME(DELTEKST(BYTT.UT(TRIMME(A1);" ";GJENTA(" ";99));99;99))

HTH


--


Regards,


Peo Sjoblom

 

[Knut]

Thank you all, works perfect.


knut

Must be similar to Swedish so I just translated the Swedish to Norwegian

=TRIMME(DELTEKST(BYTT.UT(TRIMME(A1);" ";GJENTA(" ";99));99;99))

HTH


--


Regards,


Peo Sjoblom

 

[Ron Rosenfeld]

Thank you all, works perfect.


knut

You're welcome. Thanks for the feedback.
--ron

 

[Rick Rothstein \(MVP - VB\)]

Although the OP has said he didn't want to use a macro, I just wanted to
offer the following for the archives...

Sub SecondItemBetweenSpaces()
MsgBox Split(ActiveCell.Value)(1)
End Sub

Rick

 

[Ken Wright]

Anything wrong with a formula?

=MID(A1,FIND(" ",A1)+1,FIND("[",SUBSTITUTE(A1," ","[",2))-(FIND(" ",A1)+1))

Assumes you won't have a [ in your strings, but you can always switch it for
another character.

Regards
Ken.................

 

[Ken Wright]

Apologies, didnt see Ron and Peo's formula responses.

Regards
Ken................

Anything wrong with a formula?

=MID(A1,FIND(" ",A1)+1,FIND("[",SUBSTITUTE(A1," ","[",2))-(FIND("
",A1)+1))

Assumes you won't have a [ in your strings, but you can always switch it
for another character.

Regards
Ken.................

I have a string like this in a cell: AAAA T52650 BBBB.

Need a function to return T52650.

AAAA, T52650 and BBBB can be of variable length, but there will always be
a space between the three.

Anyone???

 

[ChipButtyMan]

I have a string like this in a cell: AAAA T52650 BBBB.

Need a function to return T52650.

AAAA, T52650 and BBBB can be of variable length, but there will always bea
space between the three.

Anyone???

Hello
The sub below will look at the first 200 cells in column A & extract
the string you want to the adjacent cell in column B.
It will only work properly so long as the middle string always
contains six digits.

Sub GetMiddleString()
Set RegEx = CreateObject("VBScript.RegExp")
RegEx.Pattern = "(\s(.)(.)(.)(.)(.)(.)\s)"
For i = 1 To 200
Range("A" & i).Activate
strTest = ActiveCell.Text
valid = RegEx.test(strTest)
If valid = True Then
Set Matches = RegEx.Execute(strTest)
Range("B" & i).Value = (Matches(0))
End If
Next
Range("A1").Select
End Sub

 

[Ron Rosenfeld]

Hello
The sub below will look at the first 200 cells in column A & extract
the string you want to the adjacent cell in column B.
It will only work properly so long as the middle string always
contains six digits.

Sub GetMiddleString()
Set RegEx = CreateObject("VBScript.RegExp")
RegEx.Pattern = "(\s(.)(.)(.)(.)(.)(.)\s)"
For i = 1 To 200
Range("A" & i).Activate
strTest = ActiveCell.Text
valid = RegEx.test(strTest)
If valid = True Then
Set Matches = RegEx.Execute(strTest)
Range("B" & i).Value = (Matches(0))
End If
Next
Range("A1").Select
End Sub

Since the OP indicated that the strings can be of variable length, your
contribution would be of limited value.

If you want to return the "middle word" of a three word space separated string,
there are other ways to do it.

If you want to use regular expressions, I'd suggest something like:

RegEx.Pattern = "\S+\s+(\S+)\s+\S+"
Result = re.Replace(str, "$1")

but there are other ways to do this, too.


--ron