+ Reply to Thread
Results 1 to 7 of 7

Formula to Extract Number from a String

  1. #1
    Forum Contributor
    Join Date
    11-12-2012
    Location
    Jeddah, Saudi Arabia
    MS-Off Ver
    2010, 2013, 2016, Office 365
    Posts
    507

    Formula to Extract Number from a String

    Hi,

    Below is the text from which I need to extract the cheque number i.e. "304" to separate column.

    Payment of Your Cheque-Through: Bengaru Tahliah Branch Cheque No: 304 Ben.Name: ADNAN ISMAK RIFAE Ref: TT2102416WR8-

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: Formula to Extract Number from a String

    You’ve aske£ for a formula, but posted in the VBA section, so is it a formula or code you want?
    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.

  3. #3
    Forum Contributor
    Join Date
    11-12-2012
    Location
    Jeddah, Saudi Arabia
    MS-Off Ver
    2010, 2013, 2016, Office 365
    Posts
    507

    Re: Formula to Extract Number from a String

    Sorry, I need a formula.

  4. #4
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: Formula to Extract Number from a String

    Thread moved.

    There are instructions at the top of the page explaining how to attach your sample workbook. We need more than one solitary example.

  5. #5
    Forum Expert
    Join Date
    10-15-2018
    Location
    MA, USA
    MS-Off Ver
    2010, 2019
    Posts
    1,616

    Re: Formula to Extract Number from a String

    It is hard to give a robust solution with just one data point.

    Assuming your string is in A1, maybe:
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    This will work for Check numbers with 3 or 4 digits, but breaks for numbers less than 3 or greater than 4.

    Or maybe:
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    This will work with any length cheque number but relies on "Ben.Name" always following the cheque number.

    Both of the above work with your one data point. If neither of them work with your real data then we need to see more of that data.
    Geoff

    Did I help significantly? If you wish, click on * Add Reputation to say thanks.
    If your problem has been resolved please select ?Solved? from the Thread Tools menu

  6. #6
    Forum Contributor
    Join Date
    11-12-2012
    Location
    Jeddah, Saudi Arabia
    MS-Off Ver
    2010, 2013, 2016, Office 365
    Posts
    507

    Re: Formula to Extract Number from a String

    @GeoffW283,

    Thanks, both the formula works.

  7. #7
    Forum Expert
    Join Date
    10-15-2018
    Location
    MA, USA
    MS-Off Ver
    2010, 2019
    Posts
    1,616

    Re: Formula to Extract Number from a String

    Glad they work for you. Thanks for the feedback and rep.

+ 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] Extract first number in a string, and the three letters after the first number
    By TRICKYT57 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 07-17-2021, 07:12 PM
  2. Formula to extract largest number from a string for Politics Dissertation
    By harryspooner in forum Excel Formulas & Functions
    Replies: 12
    Last Post: 05-31-2019, 11:54 AM
  3. [SOLVED] Formula to extract number from a string
    By Bobbbo in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 03-28-2017, 03:04 AM
  4. Need Formula to extract number from text string
    By tuongtu3 in forum Excel Formulas & Functions
    Replies: 15
    Last Post: 03-28-2015, 06:01 PM
  5. [SOLVED] How to Extract A Number from String if Text within the String Equals XYZ
    By tyrsfury in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 11-15-2012, 03:30 PM
  6. [SOLVED] Extract a mainly numeric string (ISSN number) from an arbitrary string
    By Buzzed Aldrin in forum Excel Programming / VBA / Macros
    Replies: 18
    Last Post: 07-04-2012, 01:49 PM
  7. [SOLVED] How to extract Fist number, Last number from string and SUM
    By Xiaojiang in forum Excel General
    Replies: 5
    Last Post: 05-07-2012, 01:39 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