How do I replace numeric values only?

**island_monkey** · 01-20-2009, 11:34 AM

When using the replace rule in Excel, you can use "?" to represent a standard variable, e.g. if you had the text "Bottle 100g", and you set the replace rule as "????g", it would delete the "g" and the four characters before it, leaving you with "Bottle".

However, if I have lots of replace rules to make in a file, and I only want to eliminate the numerical values before a letter, how do I go about doing this?

For example, if I had the text "Dog food 10g", and I made a replace rule "??g", it would leave me with " food ". What I would really need, is a way of taking away the numeric values before the "g", which would give me "Dog food".

Therefore, is there a unique identifier for numeric and/or text values?

Or can you suggest an alternative method?

Thanks

**Rahul Nagar** · 01-20-2009, 11:52 AM

Hello Island_monkey,

I think you have to use Text to columns for separate your data.

or attached a sample file for more details.

**oldchippy** · 01-20-2009, 12:04 PM

Hi,

If all your words end with something like 110g or 20g then try this in B1

=LEFT(A1,FIND(RIGHT(A1,LEN(A1)-FIND("^^",SUBSTITUTE(A1," ","^^",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))),A1,1)-1)

**DonkeyOte** · 01-20-2009, 12:07 PM

would you ever have valid numbers preceding the end point... eg

"Dog 10 Food 2Kg"

EDIT: ignore -- didn't see OC's post which handles the above scenario anyway...

**island_monkey** · 01-20-2009, 12:16 PM

@ Rahul - I'm not sure how that would work?

@ Oldchippy - This works for eliminating the last string if it is always like this, however it doesn't resolve the problem, as what happens if I have the following text values:

Dog Food 10g
Cat food 100ml
Dog Food 10g Special Offer

Your formula would give me the following results:

Dog Food
Cat food
Dog Food 10g Special

Meaning that it would delete the "ml" unit value and not exclude the "10g" from the last value (but you did point this out in your comment already).

From the 3 example values, I would like to have the following results:

Dog Food
Cat food 100ml
Dog Food Special Offer

**martindwilson** · 01-20-2009, 12:27 PM

so now it changes, you want to keep some numbers not others! only deleting those that are g not ml , is that correct?

**Rahul Nagar** · 01-20-2009, 12:42 PM

Hello island_monkey,

Please check the attach file.

this might be help you

**island_monkey** · 01-20-2009, 12:47 PM

@ martindwilson

Nothing has changed from the original request, which was to find a variable for numeric values in the replace function. So, where "?" represents both text and numeric values, I wanted to find something that could represent just a numerical value.

But, in answer to your question, yes, I would need to define which letter comes after the numbers for the replace rule. So, in this example, I want all instances where there is a number followed by "g" to be eliminated.

@ Rahul

Thanks, I'll take a look.

**island_monkey** · 01-20-2009, 12:59 PM

@ Rahul

The problem with this Text to Columns solution is that it is dependent on the structure of the text. Furthermore, it will require concatenation at the end. If I'm dealing with big files with long product names, this might get messy. But, thanks a lot for your help.

**martindwilson** · 01-20-2009, 03:22 PM

try

=IF(OR(MID(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"0123456789"))+1,1)="g",MID(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"0123456789"))+2,1)="g",MID(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"0123456789"))+3,1)="g"),TRIM(LEFT(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"0123456789"))-2)&" "&MID(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"0123456789"))+4,255)),A1

)
also this should work its an array confirmed with ctrl+shift+enter
its shorter!

=TRIM(SUBSTITUTE(A1,(MID(A1,MATCH(TRUE,ISNUMBER(1*MID(A1,ROW($1:$1000),1)),0),COUNT(1*MID(A1,ROW($1:$1000),1))))&"g",""))

and you could easily modify it for say k instead of g (note it is case sensitive)

**island_monkey** · 01-21-2009, 04:50 AM

Hi Martin,

Thanks a lot for this, it works, this will help me a great deal!

How do I replace numeric values only?

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

How do I replace numeric values only?

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions