LinkBack URL
About LinkBacks
A19 contains file path.
B19 contains a formula which extract text name from the path.
I don't understand the logic of that formula.
A19 contains file path.
B19 contains a formula which extract text name from the path.
I don't understand the logic of that formula.
This bit tells you how many \ are in the string:
=LEN(A19)-LEN(SUBSTITUTE(A19,"\",""))
So the formula simplifies to:
=MID(A19,FIND("*",SUBSTITUTE(A19,"\","*",2))+1,LEN(A19))
This bit replaces the second \ with an *
=SUBSTITUTE(A19,"\","*",2)
Finally, the MID function looks for the ONLY * in the string and returns the characters from that position PLUS 1, to the end of the string.
=MID(A19,FIND("*",A19)+1,LEN(A19))
Glenn
None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU
B25 contains a formula which extract the last word of a string on A25.
I don't understand the logic of that.
It's probably best to keep to your first thread, as these queries are related: http://www.excelforum.com/excel-form...om-a-path.html
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. It's a universal courtesy.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
NB: as a Moderator, I never accept friendship requests.
Forum Rules (updated August 2023): please read them here.
Hi
I use this similar formula
Formula:
Please Login or Register to view this content.
instead
Formula:Please Login or Register to view this content.
to be more streamlined, although the principle is similar
The formula use the following
1) Count the number of spaces :: LEN(A25)-LEN(SUBSTITUTE(A25," ",""))
2) Substitute the last space by a char not usualy used (example CHAR(7) or "*") :: SUBSTITUTE(A25," ",CHAR(7),LEN(A25)-LEN(SUBSTITUTE(A25," ","")))
3) Locate that character (CHAR(7)) in the string :: FIND(CHAR(7),SUBSTITUTE(A25," ",CHAR(7),LEN(A25)-LEN(SUBSTITUTE(A25," ",""))))+1,255)
4) Get the substring starting at that position :: MID(A25,FIND(CHAR(7),SUBSTITUTE(A25," ",CHAR(7),LEN(A25)-LEN(SUBSTITUTE(A25," ",""))))+1,255)
5) On error returns the same string IFERROR(....;A25)
Regards
This is a duplicate post(2ND FOR THIS SUBJECT) and as such does not comply with Rule 5 of our forum rules. As you have replies in both of your threads i'll merge these 2 threads.
Regards
Fotis.
-This is my Greek whisper to Europe.
--Remember, saying thanks only takes a second or two. Click the little star * below, to give some Rep if you think an answer deserves it.
Advanced Excel Techniques: http://excelxor.com/
--KISS(Keep it simple Stupid)
--Bring them back.
---See about Acropolis of Athens.
--Visit Greece.
Formula:Please Login or Register to view this content.
Isn't there a function directly find the number of the character in a string?
LEN(A19) = 19Originally Posted by Glenn Kennedy
"*" Position is 11.
But after 11 there is not 19 characters. Why doesn't it give error?
Isn't there a function which find the position of specified occurance of a character?
In repsonse to post 8: It will return as many characters as there are, up to a maximum of 19... You can use a number (you will often see 255 chosen)
No, not necessarily. If, the character in question is unique, then FIND or SEARCH will do that, but if there are multiple instances of the character, Excel doesn't have a direct function that will find the 3 instance of that character (for example). It would be nice if "instance" was an optional argument in FIND and SEARCH. You'd need to combine SUBSTITUTE and FIND/SEARCHIsn't there a function which find the position of specified occurance of a character?
ChemistB
My 2?
substitute commas with semi-colons if your region settings requires
Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)
Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble
What about #7 entry question?
How is #7 different from #9?
#9 occurance : for example : "sivas" i want to find second occurance of "s" it's 5.
#7 number (amount) : "sivas" i want to find how many "s" there are. it's 2.
To find how many "s" there are, again it's not direct, the formula would be
=LEN(A1)-LEN(SUBSTITUTE(A1, "s",""))
Note that the SUBSTITUTE function is case sensitive.
S and s are not the same!
Biff
Microsoft MVP Excel
Keep It Simple Stupid
Let's Go Pens. We Want The Cup.
True, if you're looking for s's, might want to do
=LEN(A1)-LEN(SUBSTITUTE(LOWER(A1), "s",""))
1) Why does this formula doesn't work for B19? It returns "\windows\text.txt"
I tried it find second "\" in text and extract next 99 string in A19.
Formula:Please Login or Register to view this content.
2) Can we simplify the original formula or fix the formula which created by me?
Why do you expect it to do that? The syntax of FIND is (Find(string,in cell,starting at character number). So you have asked Excel to find position of the first / in A19, starting at character 2. The MID bit then returns that character and everything from there to character 99, or to the end of the string (whichever comes first)
Formula:Please Login or Register to view this content.
The formula on B25 extract the last word from the string on A25.
Can you explain the logic of the formula, how it works?
=IF(ISERR(FIND(" ",B25))*LEN(B25)-LEN(SUBSTITUTE(B25," ",""))=0,B25,RIGHT(B25,LEN(B25)-FIND("*",SUBSTITUTE(B25," ","*",LEN(B25)-LEN(SUBSTITUTE(B25," ",""))))))
First part counts the spaces and if there are none, return B25
=IF(ISERR(FIND(" ",B25))*LEN(B25)-LEN(SUBSTITUTE(B25," ",""))=0,B25
I do not know why they felt it necessary to include this bitISERR(FIND(" ",B25))*I would have just used
=IF(LEN(B25)-LEN(SUBSTITUTE(B25," ","")=0,B25
Part 2: In this part,
FIND("*",SUBSTITUTE(B25," ","*",LEN(B25)-LEN(SUBSTITUTE(B25," ","")))))
The Substitute function is used to replace the last space with an "*". Now FIND can be used to find the last space (since it is the only * in the string).
So this part,
RIGHT(B25,LEN(B25)-FIND("*",SUBSTITUTE(B25," ","*",LEN(B25)-LEN(SUBSTITUTE(B25," ",""))))))
pulls the number of characters from the right side of the string between where the * is (found by part 2) to the end (LEN(B25))
Does that help?
The formula which I create extracts the last word from a string. However it won't work. Why?
Formula:Please Login or Register to view this content.
1)The second argument of it works in this way;
It substracts the position of last space from the number of all characters. So it finds the character number which last word contains.
2) It finds last space in this way;
It substracts the number of all characters without spaces from the all characters.
Hi
2) "is not correct" 14-12=2 is not the position of last space
instead
finds last space position.Formula:Please Login or Register to view this content.
gives the last wordFormula:Please Login or Register to view this content.
but the third argument of FIND function means "nth"
This formula returns 3. I use it 3rd argument of FIND.
Formula:Please Login or Register to view this content.
So it should give 3th space's position of the string.
Formula:Please Login or Register to view this content.
Am I wrong?
The third funcion within FIND is starting position number, not nth.
understood.
I created a formula which should extract the last word from a string. But it extract last two words. I don't understand why?
Formula:Please Login or Register to view this content.
On second argument of the function "RIGHT"; It substitute the last space (equals the number of total spaces) with "*" and find it and substract "-1"
Because the number of characters in RIGHT(B25, # of Characters) is the count of characters from the right side of the string and
FIND("*", SUBSTITUTE(B25, " ", "*", LEN(B25)-LEN(SUBSTITUTE(B25, " ", ""))))-1 is finding the number of characters from the left side of the string (minus 1)
Last edited by ChemistB; 01-11-2016 at 01:02 PM.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks