pull specific data from cell string [need all 2 alphabets words from text string of a cell]

[Mehmud Khan]

Dear team,

Good day!

I hope all are good.

I need your help to solve below argument;

In the A1 cell, available text is "cat is not so good but drink milk 1l daily ", so I need the output in cell B2 "is so 1l"

I mean with the help of vba functions formula, is there any option to pull data which contain only 2 alphabets words.



I hope the example given above is helps to understand the issue.

I would like to highlight, the SEARCH + MID formula is not working when I tried with " ** " criteria, its mess to pull the second word, may be it due to space.

Thanks in advance,

Regards,
Mehmud

 

[AliGW]

Which version of Excel are you using?

 

[Mehmud Khan]

Which version of Excel are you using?

Hi,

Thanks for your reply!

I am using MS Excel 2016

 

[AliGW]

That's a shame - it would be very easy in 365.

 

[Mehmud Khan]

Hi,

But I have already tried with SEARCH + MID formula but it shows me incorrect results from the second word onwards.

Any clue so I can go ahead please…

Regards,
Mehmud

 

[vletm]

Mehmud Khan​

Here one sample function ... for clue ... with VBA.

 

[Mehmud Khan]

Hi vletm!

Thanks you very much....

Its works awesome.

Thanks a lot.

God bless you and your skills..

See sometimes a very small challenge is a big issue for somebody.... as I didn't have much skill in VBA so always find this platform to get the help and keep learning.

Thanks once again!

-Regards,
Mehmud

 

[p45cal]

Excel 2016 has Get & Transform Data (Power Query) built-in so the attached should work for you.
Update the data in the left table then in the right table: right-click and choose Refresh.



Separately, @vletm 's function will return a zero if the cell it's working on is empty, but this is easily fixed by adding as String to the end of the first line:
Function Mehmud_Khan(aa) As String

Here's another:

Function TwoLetterWords(rng) As String
For Each Z In Split(rng)
  If Len(Z) = 2 Then TwoLetterWords = Join(Array(TwoLetterWords, Z))
Next Z
TwoLetterWords = Application.Trim(TwoLetterWords)
End Function

 

[AliGW]

Just for anyone whoi's interested, here's a 365 solutuon (won't work in earlier versions):

=BYROW(Table1[Column1],LAMBDA(r,
LET(ts,TEXTSPLIT(r," "),l,LEN(ts),
TEXTJOIN(" ",,FILTER(ts,l=2)))))