• 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.

Alter formula to remove number from the cell.

J

Jagdev Singh

Active Member
Hi Experts

Is there anyother single formula to remove number from the cell.

I am following the below norm.

Jagdev Sinder 40-09

Formula 1 = "=RIGHT(G4,6)"
Formula 2 = "=LEFT(G4,LEN(G4)-LEN(H4))"

Regards,
JD
 
Hi JD,
If the matter is only to remove the numbers, you can go with simple substitute:

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(G4,0,""),1,""),2,""),3,""),4,""),5,""),6,""),7,""),8,""),9,"")

Regards,
 
Hi Syed,
I think you are using older version of excel, or you are trying this formula on xls file.
Try this on any new version, it is working at my end (using 2007).
 
Hi Khalid

Thanks for the formula, but I think it would be to large. I have this alternative one -

=LEFT(G4,SEARCH("-",G4,2)-3)

Regards,
JD
 
Yes your solution can be used for the specific data as you posted.
It is not removing numbers from text, it is simply doing =LEFT(G4,14)
14 is the length of your name with space at end.
 
Your formula just removed the number, but there is "-" inbetween the number. It keeps it there in the cell. I have to perform one more action of replace all on the cell to get the desire result.

Regards,
JD
 
Jagdev Singh said:
Your formula just removed the number, but there is "-" inbetween the number. It keeps it there in the cell. I have to perform one more action of replace all on the cell to get the desire result.

Regards,
JD
Click to expand...

Yes you are right, above formula only removes the number from 0 to 9.
you can amend something like:

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(G4,0,""),1,""),2,""),3,""),4,""),5,""),6,""),7,""),8,""),9,""),"-","")

Regards,
 
Just for the sake of fun..to be array entered

SUBSTITUTE(A1,MID(A1,MIN(IF(ISNUMBER(--MID(A1,ROW(OFFSET($A$1,,,LEN(A1))),1)),ROW(INDIRECT("1:"&LEN(A1))),"x")),(MAX(IF(ISNUMBER(--MID(A1,ROW(OFFSET($A$1,,,LEN(A1))),1)),ROW(INDIRECT("1:"&LEN(A1))),"x"))+1)-MIN(IF(ISNUMBER(--MID(A1,ROW(OFFSET($A$1,,,LEN(A1))),1)),ROW(INDIRECT("1:"&LEN(A1))),"x"))),"")
 
Hi Asheesh

I was trying to figure out, but failed to understand the logic you used. Could you please help me to understand it.

Regards,
JD
 
JD in simple words..I first split the text in the reference cell across different rows using MID(A1,ROW(OFFSET($A$1,,,LEN(A1))),1)..once each character is split I then identify the integers using ISNUMBER Function..

Then: IF(ISNUMBER(--MID(A1,ROW(OFFSET($A$1,,,LEN(A1))),1)),ROW(INDIRECT("1:"&LEN(A1))),"x")

assigns a row number to the charcter which is an integer now each integer will have a unique row number assigned to it..

I know I have extract everything including first and the last integer..so using MIN gives me the location of First integer in the string and MAX gives the location last Integer..This MIN and MAX is actually the row number assigned to the integer values in the string.

Once done, I use the mid function to extract the value which starts with the integer and then end with integer..

And towards the end..I use substitute function to replace that bit with nothing..

Let me know, if this doesnt help...will write a detailed note in that case..
 
Thanks Asheesh, I understood the formula little bit. I will try to understand it from my end and if stuck somewhere will get back to you.

Regards,
JD
 
You must log in or register to reply here.
Back
Top