fbpx
Search
Close this search box.

All articles with 'extract' Tag

#awesome trick – Extract word by position using FILTERXML()

Published on Sep 12, 2019 in Excel Howtos, Learn Excel
#awesome trick – Extract word by position using FILTERXML()

This is CRAZY!!!. I stumbled on a weird use for FILTERXML() while reading a forum post earlier today. So I couldn’t wait to test it. I am happy to share the results.

Say you have some text (sentence / phrase / keyword etc.) in a cell and you want to extract the nth word. Unfortunately Excel doesn’t have SPLIT() formula. So we end up writing obscenely long array formulas or use gazillion helper columns.

Here is the super sneaky trick. Use FILTERXML() instead.

Continue »

Extract currency amounts from text – Power Query Tutorial

Published on Jul 27, 2017 in Power Query
Extract currency amounts from text – Power Query Tutorial

Let’s say you got some text values and want to extract the amounts from them. Something like above.

How to go about it?

We could use a variety of techniques to extract the values.

Continue »

Extract file name from full path using formulas

Published on Oct 23, 2012 in Excel Howtos, Formula Forensics
Extract file name from full path using formulas

Today lets tackle a very familiar problem. You have a bunch of very long, complicated file names & paths. Your boss wants a list of files extracted from these paths, like below:

Of course nothing is impossible. You just need correct ingredients. I cannot help you with a strong cup of coffee, so go and get it. I will wait…

Back already? well, lets start the formula magic then.

Continue »