# [solved] Find the last numeric position in a text string

1. ## [solved] Find the last numeric position in a text string

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.

2. ## Re: Find the last number in a text string

Welcome to the forum.

For example text string is "A111" it should return 4.
Why? Your title says:

Find the last number in a text string
so why not 1???

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

A
B
C
1
A111
4
=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?

3. ## Re: Find the last number in a text string

I got your message. I changed the topic name.

4. ## Re: Find the last numeric position in a text string

OK - then the formula works correctly if entered correctly (CSE). What is your problem with CSE formulae?

5. ## Re: Find the last numeric position in a text string

No the formula does not work. If the text string is "A111" the last numeric position is 4 and not 2.

6. ## Re: Find the last numeric position in a text string

As Ali says, the formula works perfectly. BUT you do need to array enter it.

7. ## Re: Find the last numeric position in a text string

Originally Posted by Raymond18
No the formula does not work. If the text string is "A111" the last numeric position is 4 and not 2.
See post #2 where I showed you that it works. It will return 2 if you don't enter it correctly.

8. ## Re: Find the last numeric position in a text string

This one ***MIGHT*** work without array entry. try it:

=IFERROR(MATCH(1E+100,INDEX(--MID(A1, ROW( INDIRECT("1:"& LEN(A1))),1),)),0)

9. ## Re: Find the last numeric position in a text string

You may need to use ; instead of , in NL.

10. Originally Posted by AliGW
OK - then the formula works correctly if entered correctly (CSE). What is your problem with CSE formulae?
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

11. ## Re: Find the last numeric position in a 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..

12. ## Re: Find the last numeric position in a text string

The problem is I use this formula in another formula.
This is the first we've been told of this ...

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.

13. ## Re: Find the last numeric position in a text string

Originally Posted by Glenn Kennedy
This one ***MIGHT*** work without array entry. try it:

=IFERROR(MATCH(1E+100,INDEX(--MID(A1, ROW( INDIRECT("1:"& LEN(A1))),1),)),0)
You can make your formula non-Volatile by writing it this way...

Formula:
`Please Login or Register  to view this content.`

14. ## Re: Find the last numeric position in a text string

Post an example showing EXACTLY what your starting point and your FINAL end result are...

15. ## Re: Find the last numeric position in a text string

I tested it. It works fine! Great work! Thanks a lot.

16. ## Re: Find the last numeric position in a text string

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.

17. ## Re: Find the last numeric position in a text string

Originally Posted by Raymond18
I tested it. It works fine! Great work! Thanks a lot.
Just wondering if you saw what I posted in Message #13 ?

18. ## Re: Find the last numeric position in a text string

Raymond, when you reply, indicate WHO you are replying to. Otherwise massive confusion arises.

19. ## Re: Find the last numeric position in a text string

Rick... Nice one. Have some rep!

20. ## Re: Find the last numeric position in a text string

Originally Posted by Glenn Kennedy
Have some rep!
Hey, thanks!

##### Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1