+ Reply to Thread
Results 1 to 10 of 10

Remove Text characters, leaving numerical characters

  1. #1
    Registered User
    Join Date
    10-03-2010
    Location
    Portland, Oregon
    MS-Off Ver
    Excel 2013
    Posts
    38

    Remove Text characters, leaving numerical characters

    I have a column of cells that contain fuel quantities and prices. The data is entered in gallons, then an ampersand (@), then the price per gallon. Examples:

    60.5 @ 5.25
    60.5 @ $5.25
    60 @ 5.25

    I created a series of formulas to extract the gallons and price separately. This part works fine.

    My problem is that sometimes the numerical data in the cell is preceded by a location (text). Examples:

    Santa Ana 60.5 @ 5.25
    Hillsboro 25 @ $4.89
    70.5 @ $5.95

    I need a way to remove the text characters, if there are any.

  2. #2
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: Remove Text characters, leaving numerical characters

    Try
    =REPLACE(A1,1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"0123456789"))-1,"")

  3. #3
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,904

    Re: Remove Text characters, leaving numerical characters

    Try this one

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


    v A B
    1 60.5 @ 5.25 60.5 @ 5.25
    2 60.5 @ $5.25 60.5 @ $5.25
    3 60 @ 5.25 60 @ 5.25
    4 Santa Ana 60.5 @ 5.25 60.5 @ 5.25
    5 Hillsboro 25 @ $4.89 25 @ $4.89
    6 70.5 @ $5.95 70.5 @ $5.95
    If you like my answer please click on * Add Reputation
    Don't forget to mark threads as "Solved" if your problem has been resolved

    "Nothing is so firmly believed as what we least know."
    --Michel de Montaigne

  4. #4
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    15,266

    Re: Remove Text characters, leaving numerical characters

    Hi neighbor to my south,

    Try this formula for text in A1

    =SUBSTITUTE(TRIM(RIGHT(SUBSTITUTE(SUBSTITUTE(A1," @ ","~")," ",REPT(" ",100)),100)),"~"," @ ")

    PS - I like AlKey's answer better than mine!!
    One test is worth a thousand opinions.
    Click the * Add Reputation below to say thanks.

  5. #5
    Forum Expert sandy666's Avatar
    Join Date
    02-05-2015
    Location
    Any Country
    MS-Off Ver
    farerwell
    Posts
    8,749

    Re: Remove Text characters, leaving numerical characters

    with your example and if lenght of formula does not matter:
    B1:
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    and CSE
    C1:
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    and CSE also.

    result:
    Please Login or Register  to view this content.
    Last edited by sandy666; 02-01-2016 at 03:20 PM. Reason: typo
    sandy
    How to create an editor for Power Query with Notepad++ (tutorial)
    How to create timeline project with vertical today marker (2010, 2013, 2016 etc...) (examples)
    Tips for Excellent Spreadsheets

    What makes learning so hard is the amount of knowledge you have to unlearn
    Why is my program not doing what I expect?
    Because you set the wrong expectations. Rewire your brain

  6. #6
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    2007, Office 365
    Posts
    11,614

    Re: Remove Text characters, leaving numerical characters

    Edit Please ignore this formula / post. It doesn't work after all. Please see post below.

    An afterthought.

    You hadn't mentioned the possibility that text characters might be also at the end. I borrowed rather heavily from a method used by XOR LX for this. It will extract the numbers substring wherever they are in the string. It is also committed with Ctrl + Shift + Enter.
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Last edited by FlameRetired; 02-01-2016 at 06:36 PM.
    Dave

  7. #7
    Forum Expert sandy666's Avatar
    Join Date
    02-05-2015
    Location
    Any Country
    MS-Off Ver
    farerwell
    Posts
    8,749

    Re: Remove Text characters, leaving numerical characters

    Dave,
    did you try with this?

    First text 25.5 @ Middle text 5.04 Last text

    it's only curiosity
    sandy

  8. #8
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    2007, Office 365
    Posts
    11,614

    Re: Remove Text characters, leaving numerical characters

    No. Mine was only intended to take the #### @ ##### patterns out. It turns out my formula won't do that in too many cases. grrr.

    If my formula did work it would return "25.5 @ Middle text 5.04" from your example. It is supposed to find the first and last number positions then extract everything between. My intent was to take care of strings like "Santa Ana 60.5 @ 5.25 on sale now" and return "60.5 @ 5.25". I goofed though. Should have done more than "borrow heavily" and tested more thoroughly.

  9. #9
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    2007, Office 365
    Posts
    11,614

    Re: Remove Text characters, leaving numerical characters

    This the actual formula from XOR LX I failed to adapt properly.
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Apologies for the oversight.

  10. #10
    Forum Expert sandy666's Avatar
    Join Date
    02-05-2015
    Location
    Any Country
    MS-Off Ver
    farerwell
    Posts
    8,749

    Re: Remove Text characters, leaving numerical characters

    that was why I asked about it

    regards
    sandy

+ 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. macro to remove text after 250 characters?
    By wonderd in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 10-27-2015, 01:09 PM
  2. [SOLVED] VBA, remove all characters within a string, except numerical characters and full stops
    By rain4u in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 02-25-2015, 09:24 PM
  3. [SOLVED] Remove text between characters
    By Tellm in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 02-13-2015, 11:37 AM
  4. [SOLVED] How to remove certain characters from a text string
    By Student1990 in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 11-05-2013, 02:05 PM
  5. How to Remove Characters from Text..?
    By SubPar in forum Excel General
    Replies: 2
    Last Post: 11-19-2012, 05:44 PM
  6. Remove Characters to the Right of text
    By kmlloyd in forum Excel General
    Replies: 2
    Last Post: 11-24-2011, 03:16 PM
  7. remove characters from a text under a condition
    By elaine216@gmail.com in forum Excel General
    Replies: 5
    Last Post: 06-05-2006, 12:30 PM

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