Trimming characters to the left ot right of a common word/symbol/phrase.. con't.. ?
Hi , some members were nice enough to help out with a formula to trim characters off of the left side of a common word.
=TRIM(RIGHT(A1,LEN(A1)-FIND(" -in- ",A1)-5))
e.g.
common characters are "-in-"
Mary -in- Ohio
Robert -in- Nebraska
Bob -in Texas
BUT.. now I need to trim to the right of the common characters for a different task. I can't figure out how to invert the formula. Any help would be mega appreciated . thanks
Last edited by syncguy; 02-26-2012 at 03:46 PM.
Try using:
=TRIM(LEFT(A1,FIND(" -in- ",A1)))
If you liked my solution, please click on the Star -- to add to my reputation
If your issue as been resolved, please clearly state so and mark the thread as [SOLVED] using the thread tools just above the first post.
How about this.... just use LEFT/RIGHT for the side you want:
=TRIM(LEFT(SUBSTITUTE(A1," -in-",REPT(" ",256)),256))
=TRIM(RIGHT(SUBSTITUTE(A1," -in-",REPT(" ",256)),256))
_________________
Microsoft MVP 2010 - Excel
Visit: Jerry Beaucaire's Excel Files & Macros
If you've been given good help, use theicon below to give reputation feedback, it is appreciated.
Always put your code between code tags. [CODE] your code here [/CODE]
?None of us is as good as all of us? - Ray Kroc
?Actually, I *am* a rocket scientist.? - JB (little ones count!)
Geniuses ! worked perfectly . thank you both
in VBA
cells(1,1)=split(cells(1,1),"-in-")(0) ' the left part
cells(1,1)=split(cells(1,1),"-in-")(0) ' the right part.
As UDF
Please Login or Register to view this content.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks