• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Removing every number (0-9) from a text string

Xiq

Xiq

Active Member
Hi Chandoo-ers!

Got myself a question I like to ask you guys :)

I'm looking for a formula (so no VBA) that can remove every number (characters 0-9) from a text string.

For example:
  • "On floor 09 there is a free cake" --> "On floor there is a free cake"
  • "My little cat has 7 tails to tell" --> "My little cat has tails to tell"
;)
I can't think of an easy solution, so any help would be wonderful!
Xiq
 
Hi ,

If the numeric characters will all occur together , and only once in a string , then you can use the formulae in one of the Formula Challenges to isolate the numeric portion , and substitute it with a null string.

Narayan
 
Hi ,

Just as an example , this is Haseeb's formula for this situation :

=SUBSTITUTE(A1,MID(A1,MIN(IFERROR(FIND({1,2,3,4,5,6,7,8,9,0},A1),"")),MIN(IFERROR(SEARCH(CHAR(ROW($65:$90)),UPPER(A1&"A"),MIN(IFERROR(FIND({1,2,3,4,5,6,7,8,9,0},A1),""))),""))-MIN(IFERROR(FIND({1,2,3,4,5,6,7,8,9,0},A1),""))),"")

entered as an array formula , using CTRL SHIFT ENTER.

I have assumed your text string is in A1.

Narayan
 
Or you can write the formula hard way once. [Won't handle decimals]
Code: 
=TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,0,""),1,""),2,""),3,""),4,""),5,""),6,""),7,""),8,""),9,""))
 
Hi Narayan and bobhc!

Thanks for your feedback!

A couple of things though:
  1. I don't want to extract, but to remove.
  2. Unfortunately there can be multiple occurrences of numbers within the text-string, else it would be quite easy (with the things I've learned from the Formula Challenges ;)).
  3. If possible I would like a "one formula fits all"-type of solution. So I'm able to keep it in one column.
Does this clarify a few things?
--Xiq
 
shrivallabha said:
Or you can write the formula hard way once. [Won't handle decimals]
Code: 
=TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,0,""),1,""),2,""),3,""),4,""),5,""),6,""),7,""),8,""),9,""))
Click to expand...

Ah, why didn't I think of that... :oops:
 
May I blame the awesome Formula Challenges that made me think in difficult solutions?? :D
 
Good day shirivallabha

Yep read that bit I thought that if Xiq could see how to make and use the UDF he could use it with just a call to it in a cell or cells
 
You must log in or register to reply here.
Back
Top