Hi. Please help me remove string or alpha in a cell.
example:
A1 = 27541ZF
in B1, I would like to put a formula that should show 27541.
Note : Numbers (integers) in this case can go up to 10 digits and string can go up also to 10 strings. A1 can have 3 numbers with 10 strings.
Thank you in advance.
Enter with Ctrl + Shift + Enter
=LEFT(A1,MAX(IF(ISNUMBER(--MID(A1,ROW(INDIRECT("1:1024")),1)),ROW((INDIRECT("1:1024"))))))
Last edited by JieJenn; 05-10-2012 at 12:29 PM.
Thank you very much JieJenn. It worked perfectly. I have a relevant scenario I would like to ask you. The formula above remove string in the right side. Is there a way we could also remove if string happens to be on the left side of the numbers.
Example:
A1= QWE12345
B1= should say 12345
Thank you again.
If that's the case it might be easier to use a custom function. Press Alt + F11 to open the VBA window, click on Thisworkbook, right click your mouse, select Insert > Module
then copy and paste this code. When you save the file, save it as workbook macro-enabled as file type
Please Login or Register to view this content.
With your data in A2 down, try this
In B2
This should extract the first number found, including decimals, from anywhere in a string.Please Login or Register to view this content.
Last edited by Marcol; 05-10-2012 at 01:24 PM.
If you need any more information, please feel free to ask.
However,If this takes care of your needs, please select Thread Tools from menu above and set this topic to SOLVED. It helps everybody! ....
Also
اس کی مدد کرتا ہے اگر
شکریہ کہنے کے لئے سٹار کلک کریں
If you are satisfied by any members response to your problem please consider using the small Star icon bottom left of their post to show your appreciation.
Marcol. Excellent. Thank you all for all your time and help. I really appreciated it. Thank you.
Marcol... you do not have to answer this one if you do not want too. Your formula worked but can you explain how your formula process in plain simple english. Thank you.
I hoped you wouldn't ask that, it's difficult to explain in plain English!!!
Try following the steps in the Formula Auditing Tool, or the Formula Builder.
It's a formula I have used for some time now, I think it originated from DonkyOte, or daddylonglegs maybe one of them might try to explain.
Marco...Fair enough....thank you very much. I really appreciated your help. Excellent!!!!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks