+ Reply to Thread
Results 1 to 7 of 7

Separate numbers from Alphanumeric string

  1. #1
    Registered User
    Join Date
    05-06-2014
    Location
    Singapore
    MS-Off Ver
    Excel 2003
    Posts
    7

    Separate numbers from Alphanumeric string

    What is the formula to start trimming from start of number?

    Given:

    *The numbers vary in length
    *The numbers are all in the right side

    Example
    Product ABCD 13.5 oz
    Product E .5 oz
    Product FG 3 oz

    I want to generate

    13.5 oz
    .5 oz
    3 oz

    Alternatively, extracting just the numbers could also work so instead of ".5 oz", I'd only have ".5"

    Thanks!
    Last edited by Kentkopfchen; 05-06-2014 at 05:17 AM.

  2. #2
    Forum Contributor
    Join Date
    08-02-2012
    Location
    Pune
    MS-Off Ver
    Office 365 (Win 10)
    Posts
    483

    Re: Separate numbers from Alphanumeric string

    Have to use VBA for that. Please refer this
    Please make the Post as solved, when you get your answer & Click * if you like my suggestion

  3. #3
    Valued Forum Contributor Saarang84's Avatar
    Join Date
    02-19-2009
    Location
    Chennai, India
    MS-Off Ver
    XL 2003 to 2010
    Posts
    812

    Re: Separate numbers from Alphanumeric string

    Hi,

    Alternatively, you can also use the below formula.

    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Last edited by Saarang84; 05-06-2014 at 06:13 AM.
    If my assistance has helped, there is a reputation icon * on the left hand corner below the post - you can show your appreciation to the user who has helped in resolving your requirement.

    If your requirement has been solved please mark your thread as Solved.
    In the menu bar above the very first post, select Thread Tools, then select "Mark this thread as Solved".

    Kindly use [FORMULA] or [CODE] tags when posting your code.

    Regards,
    Sarang

  4. #4
    Registered User
    Join Date
    04-16-2014
    Location
    Karachi, Pakistan
    MS-Off Ver
    Excel 2007
    Posts
    26

    Re: Separate numbers from Alphanumeric string

    Select the data range.
    Go to data tab on ribbon. Select text to column then select Delimited press Next.
    Select the appropriate Delimiter(s) for your data. If text and numeric data is seprated by space then select space and click on finish button

  5. #5
    Forum Guru martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Separate numbers from Alphanumeric string

    you could try
    =TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",20)),30))
    "Unless otherwise stated all my comments are directed at OP"

    Mojito connoisseur and now happily retired
    where does code go ?
    look here
    how to insert code

    how to enter array formula

    why use -- in sumproduct
    recommended reading
    wiki Mojito

    how to say no convincingly

    most important thing you need
    Martin Wilson: SPV
    and RSMBC

  6. #6
    Registered User
    Join Date
    05-06-2014
    Location
    Singapore
    MS-Off Ver
    Excel 2003
    Posts
    7

    Re: Separate numbers from Alphanumeric string

    Of all, this has solved my problem... Thanks a lot!!!

    Quote Originally Posted by martindwilson View Post
    you could try
    =TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",20)),30))

  7. #7
    Registered User
    Join Date
    05-06-2014
    Location
    Singapore
    MS-Off Ver
    Excel 2003
    Posts
    7

    Re: Separate numbers from Alphanumeric string

    Close but not quite as it also trimmed some of the text. Thanks though!


    Quote Originally Posted by Saarang84 View Post
    Hi,

    Alternatively, you can also use the below formula.

    Formula: copy to clipboard
    Please Login or Register  to view this content.

+ 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] Incriminate numbers with in alphanumeric string
    By Karnik in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 02-20-2014, 10:25 PM
  2. Extracting Numbers from an Alphanumeric String
    By martinhardy in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 03-31-2013, 04:53 PM
  3. [SOLVED] Remove numbers from alphanumeric string. No standard format to the string.
    By ricunger in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 10-20-2012, 01:43 PM
  4. Trim to separate numbers from alphanumeric strings
    By VBisgreat in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 04-13-2011, 12:11 PM

Tags for this Thread

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