Get text string from another using wildcards...

D

Dan1000

Board Regular
Joined
Nov 4, 2006
Messages
210
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
 

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).
Sort by date Sort by votes
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:
 
Upvote 0
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?
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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 ;)
 
Upvote 0
Excel Workbook
ABCD
1Word to search for:Number of Words to returnResult
2The dog chases catsdog2dog chases
Sheet1
 
Upvote 0
You must log in or register to reply here.

Similar threads

T
  • Solved
How to extract text from cell between "(" and ")" ?
Replies
3
Views
520
Fluff
Fluff
Y
  • Solved
Highlighting selected words in a column.
Replies
6
Views
136
Akuini
Akuini
B
  • Solved
Removing characters from the front of a text string
2
Replies
16
Views
445
awoohaw
awoohaw
M
  • Solved
Extracting specific text from a thread
2
Replies
19
Views
1K
Fluff
Fluff
tourless
  • Question
Capture and remove last two in a string
Replies
3
Views
490
duggie33
D

Forum statistics

Threads
1,214,920
Messages
6,122,269
Members
449,075
Latest member
staticfluids

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top