Extract Text Between Spaces? Formulas? LET Function? Power Query? 5 Examples! EMT 1736.

Learn how to extract an ID number between a start and end space delimiter.
Topics:
1. (00:00) Introduction
2. (00:32) Worksheet Formula with 6 functions.
3. (02:21) LET function to remove formula elements that are repeated.
4. (03:50) Worksheet Formula with 5 functions.
5. (04:47) INDEX and FILTERXML function example.
6. (05:17) Power Query Extract Between Delimiters.
7. (05:58) Summary, Closing and Video Links

[excelisfun]

Topics:
1. (00:00​) Introduction
2. (00:32​) Worksheet Formula with 6 functions.
3. (02:21​) LET function to remove formula elements that are repeated.
4. (03:50​) Worksheet Formula with 5 functions.
5. (04:47​) INDEX and FILTERXML function example.
6. (05:17​) Power Query Extract Between Delimiters.
7. (05:58​) Summary, Closing and Video Links

[REALBRASA]

Bonus 3 (05:17​) Power Query Extract Between Delimiters."
Just helped me ! I were having a bad time trying with the syntaxe.
Thanks this video I've solved my task and now I can learn the right sintaxe.
Thank you very much !

[Softwaretrain]

Thanks, Mike,
If he doesn't want to be dynamic Flash Fill will be easier but for linkable results, all of your formulas are great also PQ.

my try are these two:

=LET(f, FIND(" ", B6),
c, CHAR(160),
s, SUBSTITUTE(B6, " ", c, 2),
r, MID(B6, f+1, FIND(c, s)-1-f), r)

---

=LET(f, SEARCH(" ", B6)+1,
m, MID(B6, f, 255),
s, SEARCH(" ", m)-1,
r, LEFT(m, s), r)

[edge]

Im an old school guy... as not all has office 365.
i love the LET function as well... and Power Query is awesome!
Thanks Mike!

[pedjanbgd]

I remember this two years ago when I was learning excel formulas. Bonus formula #+1
=TRIM(MID(SUBSTITUTE(B6, " ", REPT(" ", LEN(B6))), LEN(B6), LEN(B6)))

[vivianyan]

Great teaching. Clear logic, neat word, simple to follow cues.

[deninsrmic]

Great tutorial Mike, Power Query was definitely the easiest one to extract data. "Old school" text functions are for me simply excellent as described in many previous videos of yours handling text extraction. I have simply got used to using them. LET is quite interesting to use in text extraction, not quite acquainted with it fully, but now its worth paying more attention to this function. Thanks

[chrism]

Wow, really brilliant Mike! Great use of LET! But I like the PQ solution the best.

[celia]

Power query is so easy in this aspect! Thanks Mike!

[SyedMuzammilMahasanShahi]

Thanks for this EXCELlent video Mike. Stay blessed.

[ogwalfrancis]

Bonus 3 Power Query method is the best 👍👍👍

[Anthony_Lecoq]

Thanks, All solution are great. To chose the best one it depends of the size of the data set, the version of excel used by users, and their needs.

[komanguy]

Wow! FILTERXML is amazing! Great learning as usual!

[jaychowdhury]

Power query rocks.. wit just few mouse clicks u get d desired result.. although formulas r d best for faster processing and auto refreshes..

[montmraaa]

In this formula the "2" toward the end of the formula just before the FIND function can be changed to extract the Nth element between the spaces.
=MID(A2, FIND(" ", A2)+1, FIND(CHAR(160), SUBSTITUTE(A2, " ", CHAR(160), 2))-FIND(" ", A2)-1)

[canirmalchoudhary]

I absolutely liked *POWER QUERY* method

[johnborg]

Thanks Mike. Formulas are a better way to go. It's more fun while you're building it. : ) : )

[bishalchowdhury]

Sir this video is awesome. I like the 1st old school version and the power query version. Thank you sir for your teaching. 👍👍👍

[wayneedmondson]

Awesome Mike! I like old school LEFT/REPLACE/FIND. Also great w/LET. And Power Query makes it so easy! Here's another one, just for fun: =LEFT(MID(B6, FIND(" ", B6)+1, LEN(B6)), FIND(" ", MID(B6, FIND(" ", B6)+1, LEN(B6)))-1). Could be done w/LET also. Thanks for the text extraction workout. Always great to know multiple methods :)) Thumbs up!!

[SantiagoMolero]

Definitely Power Query. Thanks for these great videos!