I tried this formula:
=AGGREGATE(14,6,FIND({1,2,3,4,5,6,7,8,9,0},A1,ROW(INDIRECT("1:"&LEN(A1)))),1)
For example text string is "A111" it should return 4. Now it returns 2.
I don't want to use array entry (crtl+shift+enter). I need the position to use the formula within another formula. Can someone help me with the formula?
Thank you in advance.
Last edited by Raymond18; 01-15-2022 at 06:43 AM.
Welcome to the forum.
Why? Your title says:For example text string is "A111" it should return 4.
so why not 1???Find the last number in a text string
You are not telling us everything and have provided just one example that does not match what you say you want to do ...
By the way:
AliGW on MS365 Insider (Windows) 64 bit
A111 =AGGREGATE(14,6,FIND({1,2,3,4,5,6,7,8,9,0},A1,ROW(INDIRECT("1:"&LEN(A1)))),1)
Sheet: Sheet1
You may need to enter it with CSE.
Why do you not want formulae that use CSE? What is the perceived problem?
Last edited by AliGW; 01-14-2022 at 11:55 AM.
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.
I got your message. I changed the topic name.
OK - then the formula works correctly if entered correctly (CSE). What is your problem with CSE formulae?
No the formula does not work. If the text string is "A111" the last numeric position is 4 and not 2.
As Ali says, the formula works perfectly. BUT you do need to array enter it.
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.
Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh
See post #2 where I showed you that it works. It will return 2 if you don't enter it correctly.Originally Posted by Raymond18
This one ***MIGHT*** work without array entry. try it:
=IFERROR(MATCH(1E+100,INDEX(--MID(A1, ROW( INDIRECT("1:"& LEN(A1))),1),)),0)
You may need to use ; instead of , in NL.
The problem is I use this formula in another formula. That’s why CSE does not work. I need separate my text string after the last numeric position in the text string
Post a sample workbook: instructions are in yellow banner at top of page.
UPDATE: Glenn's formula works on a single string but we don't have an example of your context..
Last edited by JohnTopley; 01-15-2022 at 04:36 AM.
If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.
This is the first we've been told of this ...The problem is I use this formula in another formula.
Please do as John says.
There are instructions at the top of the page explaining how to attach your sample workbook.
A good sample workbook has just 10-20 rows of representative data that has been desensitised. It also has expected results mocked up, relevant cells highlighted and a few explanatory notes.
You can make your formula non-Volatile by writing it this way...
Formula:
Please Login or Register to view this content.
Last edited by Rick Rothstein; 01-15-2022 at 05:01 AM.
Post an example showing EXACTLY what your starting point and your FINAL end result are...
I tested it. It works fine! Great work! Thanks a lot.
If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.
Also, if you have not already done so, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of all those who offered help.
Just wondering if you saw what I posted in Message #13 ?
Raymond, when you reply, indicate WHO you are replying to. Otherwise massive confusion arises.
Rick... Nice one. Have some rep!
Hey, thanks!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks