Get text string from another using wildcards...

[Dan1000]

Is there a simple excel formula which will extract a text string from another text string.

The text string to be extracted will usually start in the middle of a text string (but may be the first or last part of the string).

The text string always begins with at least the same 3+ characters.


If / when this can be done, it would be useful to also control the number of words being extracted starting from the start of the text string being extracted, that is rather than use a wild card (*) to take all characters from the end of the text string.

Kind regards

 

[hiker95]

Dan1000,

Can we see some of the complete text strings, and what you are pulling out of the text strings?

You will generally get much more help (and faster) in this forum if you can post your small samples (what you have and what you expect to achieve) directly in the forum.

To attach screenshots, see below in my Signature block: Post a screen shot with one of these:

 

[barry houdini]

You can use MID to extract some text like this

=MID(A1,SEARCH("xyz",A1),30)

that will extract 30 characters starting with the first instance of "xyz" in A1.......if you want a specific number of words rather than characters that will be more complicated, are the words simply separated by single spaces?

 

[Dan1000]

Thanks guys...

=MID(A1,SEARCH("xyz",A1),30) works nicely, thank you.

Words seperated by single spaces...just need some control over the numbers of words being extracted - so I can decide whether two, three or more words are taken


Regards

 

[barry houdini]

Try this version

=MID(A1,SEARCH("xyz",A1),FIND("^^",SUBSTITUTE(MID(A1,SEARCH("xyz",A1),LEN(A1))," ","^^",3))-1)

the red 3 defines the number of words - change as required

 

[Dan1000]

Spot on - thanks...

...can you escape from a tank of water whilst hanging upside down and in a straight jacket and chained up, in less than 1 minute?


Kind regards

 

[Scott Huish]

Excel Workbook
	A	B	C	D
1		Word to search for:	Number of Words to return	Result
2	The dog chases cats	dog	2	dog chases
Sheet1

 

[Dan1000]

Thanks, like that one HOTPEPPER


Regards

'Naga Viper'

 

[barry houdini]

...can you escape from a tank of water whilst hanging upside down and in a straight jacket and chained up, in less than 1 minute?

Not any more.....but HOTPEPPER can, apparently