• 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 all charcters from cell but numbers

S

Suresh

New Member
I want to remove special characters and text and etc but to keep only numbers. Please let me know how it can be done in excel.

For example if the cell content is like #568775, then it should returns as 568755.

I want this to be done using single formula
 
Hi Suresh,
If VBA is out of the question (and I can't help you with that), then it would be helpful to have a few more examples. Either that, or you get an big and ugly formula with a lot of nested SUBSTITUE's for each character to remove.
 
Hi Xiq,
One approach to try is to multiply each digit with the power of 10, starting with the rightmost digit, and skipping anything that is non-numeric.

Cheers,
Sajan.
 
Hi Sajan,

I tried subsititute method and as you said for each and every special character I need to type seperately. Is there any wayout that I can keep only numbers in the cell and remove rest using single formula
 
Hi Suresh,

Like I already said, it would be helpful if you have a few more examples. We can not magically know how your data looks like and anticipate every possible occurrence to deal with.

For example:
Code: 
=RIGHT("#568775",6)
Should give the expected result of "568755"... but I don't think this would be the solution you expect.
 
Hi,
To get rid of any non-numeric characters from a string, assuming that the resulting extract is a valid Excel number, you can try one of the following approaches:
For a string in cell A28:
=SUM(ROUND(MOD(SMALL(IFERROR(ROW(OFFSET(A$1,,,LEN(A28)))+MID(A28,1+LEN(A28)-ROW(OFFSET(A$1,,,LEN(A28))),1)%, LEN(A28)+1), ROW(OFFSET(A$1,,,SUM(N(ISNUMBER(0+MID(A28,ROW(OFFSET(A$1,,,LEN(A28))),1))))))),1)*100,0)*(10^(ROW(OFFSET(A$1,,,SUM(N(ISNUMBER(0+MID(A28,ROW(OFFSET(A$1,,,LEN(A28))),1))))))-1)))&""
enter with Ctrl + Shift + Enter

Here is a formula gem from Lori that does the same thing. Again, for a string in cell A28:
=NPV(-0.9,,IFERROR(MID(A28,1+LEN(A28)-ROW(OFFSET(A$1,,,LEN(A28))),1)%,""))&""
enter with Ctrl + Shift + Enter

Cheers,
Sajan.
 
Hi Venkata,
As you discovered, a formula can be greatly simplified to meet a specific need. Now, how would you generalize your formula if the alphabets are not limited to the left-most position? (For example, "ABC12345")

-Sajan.
 
thank you, i found this on the stackoverflow and modified little bit as our requirement.
this function simply replace the numbers and count words

Code: 
SUM(LEN(A1)-LEN(SUBSTITUTE(A1,{1,2,3,4,5,6,7,8,9},"")))-LEN(A1)

and with the help of above function we can do what u have asked.

Code: 
=MID(A1,LEN(A1)-SUM(LEN(A1)-LEN(SUBSTITUTE(A1,{1,2,3,4,5,6,7,8,9},"")))+1,SUM(LEN(A1)-LEN(SUBSTITUTE(A1,{1,2,3,4,5,6,7,8,9},""))))
 
Hi Venkata,
Nice!
You can shorten it slightly as follows:
=MID(A1,LEN(A1)-SUM(LEN(A1)-LEN(SUBSTITUTE(A1,{1,2,3,4,5,6,7,8,9},"")))+1,LEN(A1))

Give some thought to how you would extract numbers from the following strings also:
1234ABC --> result should be 1234
ABC1234DEF --> result should be 1234
AB12CD34EFG --> result should be 1234

-Sajan.
 
Thank you, and here you go

1234ABC
Code: 
=LEFT(A1,LEN(A1)-SUM(LEN(A1)-LEN(SUBSTITUTE(A1,{"A","B","C","D","E","F","G","H"},""))))

ABC1234DEF
Code: 
=MID(A2,LEN(A2)-SUM(LEN(A2)-LEN(SUBSTITUTE(A2,{"A","B","C","D","E","F","G","H"},""))),LEN(A2)-SUM(LEN(A2)-LEN(SUBSTITUTE(A2,{"A","B","C","D","E","F","G","H"},""))))

AB12CD34EFG

Code: 
=MID(A3,LEN(A3)-SUM(LEN(A3)-LEN(SUBSTITUTE(A3,{"A","B","C","D","E","F","G","H"},"")))-1,LEN(A3)-9)&""&MID(RIGHT(A3,SUM(LEN(A3)-LEN(SUBSTITUTE(A3,{"A","B","C","D","E","F","G","H"},"")))-2),1,2)
 
thank you again sajan, and narayank991,
sorry for the late I work at nights
if i understand you guys correctly this time, this below code extract string and give you result, i dont know how to do tihs with excel formulas but it can be done with VBA very easily.

Code: 
Sub myTest()
    x = Array("A", "B", "C", "D", "E", "F", "G", "H", "I")
    For i = 0 To UBound(x)
        For j = 1 To Cells(Rows.Count, 1).End(xlUp).Row
            Cells(j, 1).Replace _
            What:=x(i), Replacement:="", _
            SearchOrder:=xlByColumns, MatchCase:=True
        Next j
    Next i
End Sub

we can use substitute

Code: 
Sub myTest()
    x = Array("A", "B", "C", "D", "E", "F", "G", "H", "I")
    For i = 0 To UBound(x)
        For j = 1 To Cells(Rows.Count, 1).End(xlUp).Row
            Cells(j, 1) = Application.Substitute(Cells(j, 1), _
                x(i), "")
        Next j
    Next i
End Sub
 
Still I did not get the right solution.

I want to remove all the characters in a cell but number I want to keep.


Three examples given below:

Input OutPut

#567895, 567895

,56#751% 58751

ac881$45 88145


Please let me know if anybody could help with excel sheet attached
 
Hi Suresh ,

Explaining the formula is , in my opinion , impossible ; it is just something that works.

The portion :

IFERROR(MID(A1,1+LEN(A1)-ROW(OFFSET(A$1,,,LEN(A1))),1)%,""))

is just isolating each character in the input string , starting from the end of the string , and working its way towards the start of the string ; the % sign converts the digit into a number divided by 100 i.e. if the digit 5 occurs in the input string , it will be converted to 0.05. If the character cannot be converted to a numeric value , it generates an error , which then replaces that character by an empty string "".

The NPV function then does some magic by which all these individual divided by 100 values are combined together to give one single number !

Narayan
 
Awesome Mr. Narayan. It is my first experience with chandoo and I am very happy that I got the resolution. I look forward your kind support in future as well.
 
Sajan said:
Hi,
Here is a formula gem from Lori that does the same thing. Again, for a string in cell A28:
=NPV(-0.9,,IFERROR(MID(A28,1+LEN(A28)-ROW(OFFSET(A$1,,,LEN(A28))),1)%,""))&""
enter with Ctrl + Shift + Enter

Cheers,
Sajan.
Click to expand...

@Sajan

The formula =NPV(-0.9,,IFERROR(MID(A1,1+LEN(A1)-ROW(OFFSET(A$1,,,LEN(A1))),1)%,""))
is Seriously Sick

Expect it to appear in a Formula Forensics very soon
Well as soon as my brain stops imploding

Hui...
 
Hi Hui,
The NPV formula is actually from Lori (that she posted to Google Groups a few years ago). I came across it recently and fell in love with its beauty too!

I was thinking of a Formula Forensics article that compiles a few different techniques that can be used to extract numbers. I will start working on it.

The magic of NPV is the NPV calculation formula, where each term is multiplied by the inverse of (1+rate)^n, where n is the nth term in the series. e.g. (1+rate)^1, (1+rate)^2, etc. By using different values for rate, we can get different results. In this case, using -0.9 gives us 1+rate=1+-0.9=0.1. So we get values like {0.1;0.01;0.001;0.0001;0.00001}. Taking the inverse of this gives us {10;100;1000;10000;100000} etc. Combined with the fact that NPV skips text values, we get the desired results.

Regards
Sajan.
 
Hello Venkata,
I am assuming that you are referring to your VBA code above. Thanks for putting that together. I will look to one of the forum VBA experts to comment on it since I stay away from VBA as much as possible!

-Sajan.
 
Hi ,

I'd like to add two points to Sajan's explanation of the internal working of the NPV function :

1. It is absolutely essential to reverse the order of the digits in order to get the correct value ! Thus , for the first string :

#567895,

suppose we enter the individual characters # , 5 , 6 , 7 , 8 , 9 , 5 , , ( the last comma ) in the range A1 through A8 , and enter the formula :

=NPV(-0.9,,A1:A8)

the result will be 59876500 !

Of course this follows from Sajan's explanation that the first value is being multiplied by 10 , the next by 100 , the next by 1000 , and so on ; in a mathematical number , the number is being multiplied by multiples of 10 , starting from the right most digit and progressing to higher multiples as we move to the left. Hence when we are progressively looking at each character of the input string , we need to start from the right and move to the left.

2. It is absolutely essential to have an additional comma ( , ) between the first parameter -0.9 , and the rest of the formula ; this is because the syntax of the NPV function is NPV(rate,value1,value2,value3,....) ; as Sajan has explained , each of the values is being multiplied by a factor of 10 ; this makes it imperative that the first value be 0 ; if not , the first digit will be multiplied by 10 , whereas the % symbol within the formula has divided by 100 ; this will result in a decimal output.

Thus , if we use the following formula :

=NPV(-0.9,IFERROR(MID(A1,1+LEN(A1)-ROW(OFFSET(A$1,,,LEN(A1))),1)%,""))

without the additional comma ,

the output will be 56789.5

Narayan
 
You must log in or register to reply here.
Back
Top