LinkBack URL
About LinkBacks
Hi, please help extracting only the numbers before string "all", please see an example attached. Need a formula to get the results as in column B. Thank you
Hi, please help extracting only the numbers before string "all", please see an example attached. Need a formula to get the results as in column B. Thank you
If the data is as you showed.
=MID(A3,SEARCH("all",A3,1)-4,3)
Notice my main language is not English.
I appreciate it, if you reply on my solution.
If you are satisfied with the solution, please mark the question solved.
You can add reputation by clicking on the star * add reputation.
will the ALL always be at the end of the text string
and the numbers can they be any length ?
and so the number will always be at the 2nd space from the end of the string
Wayne
if my assistance has helped, and only if you wish to , there is an "* Add Reputation" on the left hand side - you can add to my reputation here
If you have a solution to your thread - Please mark your thread solved do the following: >
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.
Thank you! There is an issue when the numbers is only 1 digits, see file please.
Please try
for row 2
=VALUE(TRIM(RIGHT(SUBSTITUTE(TRIM(SUBSTITUTE(A2,"all",""))," ",REPT(" ",LEN(TRIM(SUBSTITUTE(A2,"all",""))))),LEN(TRIM(SUBSTITUTE(A2,"all",""))))))
note : all data must ended with 'all'
Regards.
My English is very poor, so please be patient >_<"
My Dynamic Related Dropdown post:
https://www.excelforum.com/excel-for...ml#post5657376
https://www.excelforum.com/excel-for...ml#post5655711
https://www.excelforum.com/excel-for...ml#post5655762
Or try this:
=--RIGHT(SUBSTITUTE(LEFT(A2,FIND(" all",A2)-1)," ",REPT(" ",100)),100)
Please try
=-LOOKUP(0,-RIGHT(LEFT(A2,FIND("all",A2)-2),{1,2,3,4,5}))
Another option:
=LET(a,FIND(" all",A2),b,LEFT(A2,a-1),c,MAX(IF(MID(b,SEQUENCE(LEN(b)),1)=" ",SEQUENCE(LEN(b)),0)),MID(A2,c+1,a-c)+0)
Try this. In C2
Formula:
Please Login or Register to view this content.
Pl note
Array formula should be confirmed with Ctrl+Shift+Enter keys together.
If answere is satisfactory press * to add reputation.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks