EXTRACT TEXT

[excelbetter]

I am trying to figure out how to extract text within a string of text in a cell.

Let's assume the string is in A1

The string is something like this:
http://media.domain.com/media/stock/thumb/design-21-hue.jpg

I am trying to extract the word "HUE" that appears before the period from the right side.

The word "HUE" might sometimes be a longer strong of text, not just 3 characters.

How do I do this and put the data I'm trying to extract in cell B1?

TY!!

 

[Rick Rothstein]

Will the word always have a dash in front of it?

 

[sandy666]

with Power Query

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Between = Table.AddColumn(Source, "Result", each Text.BetweenDelimiters([source], "-", ".", {0, RelativePosition.FromEnd}, 0), type text),
    TSC = Table.SelectColumns(Between,{"Result"})
in
    TSC

source		Result
http://media.domain.com/media/stock/thumb/design-21-hue.jpg		hue

btw. update your profile about Excel version, please

 

[excelbetter]

Will the word always have a dash in front of it?

Yes, it will always have a dash before it and a period after it.

 

[Peter_SSs]

One way by formula:

20 07 03.xlsm
	A	B
1	http://media.domain.com/media/stock/thumb/design-21-hue.jpg	hue
Extract

Cell Formulas
Range	Formula
B1	B1	=TRIM(LEFT(RIGHT(SUBSTITUTE(SUBSTITUTE(A1,".","-"),"-",REPT(" ",50)),100),50))

 

[Rick Rothstein]

Give this formula a try...

=TRIM(LEFT(RIGHT(SUBSTITUTE(SUBSTITUTE(A1,"-","."),".",REPT(" ",300)),600),300))

 

[excelbetter]

Excellent. TY!

One way by formula:

20 07 03.xlsm
	A	B
1	http://media.domain.com/media/stock/thumb/design-21-hue.jpg	hue
Extract

Cell Formulas
Range	Formula
B1	B1	=TRIM(LEFT(RIGHT(SUBSTITUTE(SUBSTITUTE(A1,".","-"),"-",REPT(" ",50)),100),50))

 

[excelbetter]

Perfect! TY

Give this formula a try...

=TRIM(LEFT(RIGHT(SUBSTITUTE(SUBSTITUTE(A1,"-","."),".",REPT(" ",300)),600),300))