# Remove Text characters, leaving numerical characters

1. ## 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. ## 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. ## Re: Remove Text characters, leaving numerical characters

Try this one

Formula:
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

4. ## 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!!

5. ## Re: Remove Text characters, leaving numerical characters

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

result:
Please Login or Register  to view this content.

6. ## 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:
Please Login or Register  to view this content.

7. ## 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. ## 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. ## Re: Remove Text characters, leaving numerical characters

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

10. ## Re: Remove Text characters, leaving numerical characters

that was why I asked about it

regards
sandy

#### Thread Information

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