+ Reply to Thread
Results 1 to 20 of 20

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

  1. #1
    Registered User
    Join Date
    01-14-2022
    Location
    Amsterdam
    MS-Off Ver
    2016
    Posts
    5

    [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.
    Last edited by Raymond18; 01-15-2022 at 06:43 AM.

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel (Windows 11 64-bit)
    Posts
    59,461

    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?
    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" to anyone who has offered you help in your thread. You can reward them by clicking on * Add Reputation below theur user name on the left, if you wish.


    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.

  3. #3
    Registered User
    Join Date
    01-14-2022
    Location
    Amsterdam
    MS-Off Ver
    2016
    Posts
    5

    Re: Find the last number in a text string

    I got your message. I changed the topic name.

  4. #4
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel (Windows 11 64-bit)
    Posts
    59,461

    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. #5
    Registered User
    Join Date
    01-14-2022
    Location
    Amsterdam
    MS-Off Ver
    2016
    Posts
    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. #6
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... based in Ireland... but I could be anywhere in the world
    MS-Off Ver
    Office 365 for PC.
    Posts
    33,880

    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.
    Glenn



  7. #7
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel (Windows 11 64-bit)
    Posts
    59,461

    Re: Find the last numeric position in a text string

    Quote Originally Posted by Raymond18 View Post
    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. #8
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... based in Ireland... but I could be anywhere in the world
    MS-Off Ver
    Office 365 for PC.
    Posts
    33,880

    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. #9
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... based in Ireland... but I could be anywhere in the world
    MS-Off Ver
    Office 365 for PC.
    Posts
    33,880

    Re: Find the last numeric position in a text string

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

  10. #10
    Registered User
    Join Date
    01-14-2022
    Location
    Amsterdam
    MS-Off Ver
    2016
    Posts
    5
    Quote Originally Posted by AliGW View Post
    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. #11
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    22,652

    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..
    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.

  12. #12
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel (Windows 11 64-bit)
    Posts
    59,461

    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. #13
    Forum Guru
    Join Date
    04-23-2012
    Location
    New Jersey, USA
    MS-Off Ver
    Excel 2010, 2019
    Posts
    2,197

    Re: Find the last numeric position in a text string

    Quote Originally Posted by Glenn Kennedy View Post
    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: copy to clipboard
    Please Login or Register  to view this content.
    Last edited by Rick Rothstein; 01-15-2022 at 05:01 AM.

  14. #14
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... based in Ireland... but I could be anywhere in the world
    MS-Off Ver
    Office 365 for PC.
    Posts
    33,880

    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. #15
    Registered User
    Join Date
    01-14-2022
    Location
    Amsterdam
    MS-Off Ver
    2016
    Posts
    5

    Re: Find the last numeric position in a text string

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

  16. #16
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel (Windows 11 64-bit)
    Posts
    59,461

    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. #17
    Forum Guru
    Join Date
    04-23-2012
    Location
    New Jersey, USA
    MS-Off Ver
    Excel 2010, 2019
    Posts
    2,197

    Re: Find the last numeric position in a text string

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

  18. #18
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... based in Ireland... but I could be anywhere in the world
    MS-Off Ver
    Office 365 for PC.
    Posts
    33,880

    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. #19
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... based in Ireland... but I could be anywhere in the world
    MS-Off Ver
    Office 365 for PC.
    Posts
    33,880

    Re: Find the last numeric position in a text string

    Rick... Nice one. Have some rep!

  20. #20
    Forum Guru
    Join Date
    04-23-2012
    Location
    New Jersey, USA
    MS-Off Ver
    Excel 2010, 2019
    Posts
    2,197

    Re: Find the last numeric position in a text string

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

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Find the space in a text string, closest to a certain number
    By ozbrian in forum Excel General
    Replies: 2
    Last Post: 12-16-2020, 08:49 PM
  2. Replies: 2
    Last Post: 01-05-2018, 12:31 AM
  3. Find Number String in a series for Text and numbers
    By GarMerr in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 09-12-2017, 01:51 PM
  4. Replies: 5
    Last Post: 07-10-2017, 10:36 AM
  5. [SOLVED] Find partial text string within another text string return original text into cell.
    By mikey42979 in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 06-17-2013, 02:58 PM
  6. Find row number of text string in a range of cells
    By Steven Fleck in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 01-06-2013, 08:38 PM
  7. Find number within a text string using VBA
    By newbie1234 in forum Excel General
    Replies: 10
    Last Post: 09-05-2009, 03:05 AM

Bookmarks

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