How to find specific text within a string and remove everything before and after using space

[crackod93]

hi, need some help please, looking for a formula which will do this:

original text = Hello 123 3DA £14.99
output text = 3DA

original text = Hello 123 33DA £14.99
output text = 33DA

original text = Hello 123 333DA £14.99
output text = 333DA

basically find DA in the middle, then remove EVERYTHING after it and everything BEFORE + INCLUDING the space before it whilst keeping the unknown number of values where you see the 3's

thanks everyone!

 

[a massive zebra]

=mid(b6,find("~",substitute(b6," ","~",2)),find("~",substitute(b6," ","~",3))-find("~",substitute(b6," ","~",2))) Where the first entry is in cell B6, then copy down.

 

[crackod93]

=mid(b6,find("~",substitute(b6," ","~",2)),find("~",substitute(b6," ","~",3))-find("~",substitute(b6," ","~",2))) Where the first entry is in cell B6, then copy down.

thanks for sending this through, but im having some problems with this.

the string which im looking in may not always conform to what i had shown as the 'original text' some times the text before and after this can be different, the only thing i can rely upon is the DA part and that there WILL be a space before and after it

so basically i need to take wherever DA is found and find the space before that and remove everything before it including the space and then anything after the DA too

thanks again

 

[XOR LX]

Hi,

=TRIM(MID(SUBSTITUTE(A1," ",REPT(" ",LEN(A1))),FIND("DA",SUBSTITUTE(A1," ",REPT(" ",LEN(A1))))-LEN(A1)+2,LEN(A1)))

Regards

 

[Peter_SSs]

You could also try

=TRIM(RIGHT(SUBSTITUTE(LEFT(A1,FIND("DA",A1)+1)," ",REPT(" ",100)),100))

 

[crackod93]

Hi,

=TRIM(MID(SUBSTITUTE(A1," ",REPT(" ",LEN(A1))),FIND("DA",SUBSTITUTE(A1," ",REPT(" ",LEN(A1))))-LEN(A1)+2,LEN(A1)))

Regards

this worked a treat - thank you so much!!!!

 

[XOR LX]

this worked a treat - thank you so much!!!!

Sure - though you should prefer Peter's. Less function calls!

Regards