I am getting strange results from a formula and wonder if anyone could explain as to what is occurring and what I need to do to overcome the issue. I have attached a sample file showing precisely what the issue is and highlighted the problem lines. Thanks
It's because of this...
=MID(B3,SEARCH(LEFT(TRIM(B3),1),B3), SEARCH(RIGHT(TRIM(B3),1),B3) -SEARCH(LEFT(TRIM(B3),1),B3)+1)
When the data string contains duplicate characters like this...
\298-T2020
The highlighted section of the formula finds the leftmost instance of the character...
\298-T2020
Biff
Microsoft MVP Excel
Keep It Simple Stupid
Let's Go Pens. We Want The Cup.
What are trying to do with the formula?
Looking at the non-highlighted results the =TRIM(B3) would get the same result.
Tony - thanks for the response. That explains what is occurring. Do you have any suggestions as to how I might overcome this?
Hi
You formula fail because B8 as two "F"
What I am trying to do is to remove the leading and trailing spaces but leave any spaces that occur between the words. If I use =Trim(B3) then all spaces are removed. Cheers
But there are no spaces between words in your test data! Perhaps you need to provide a more realistic sample?
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
No, TRIM will remove multiple spaces between words, but will leave a single space instead.
Pete
AliGW - The formula is written to do precisely what I have explained but I now have this other problem in that it deletes the duplicates.
And TRIM do not remove spaces between words.
Pete, sorry I should have been more specific and said multiple spaces between words.
So the question is - does anyone know of a way that I can remove JUSTthe leading and trailing spaces? Thanks
Try ...
=MID(B3,FIND("\",B3,1),LOOKUP(2,1/(MID(B3,ROW(INDIRECT("1:"&LEN(B3))),1)<>" "),ROW(INDIRECT("1:"&LEN(B3))))-FIND("\",B3,1)+1)
=MID(LEFT(B3,SEARCH(REPT("^",10),SUBSTITUTE(B3," ","^")&REPT("^",10))-1),SEARCH("\",B3),256)
Try this and copy towards down
Please Login or Register to view this content.
Samba
Say thanks to those who have helped you by clicking Add Reputation star.
John & NFL thank you both for your solutions they both work a treat. I will mark as solved.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks