Hi,
I have few numbers containing alpha-numeric value.
Need a formula to get the result (as per the given example) i.e. to Search the Alphabet and get the result (by removing the preceding numbers)
Ex:
Data = Result (Required)
1F1 = F1
12W2 = W2
1000F12 = F12
5000W51 = W51
Pl. help.
Thanks,
Nagesh.
I wonder if there's a shorter way?
=RIGHT(A1,LEN(A1)+1-MATCH(TRUE,ISERROR(VALUE(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))),0))
Array formula, use Ctrl-Shift-Enter
Regards
Special-K
Ensure you describe your problem clearly, I have little time available to solve these problems and do not appreciate numerous changes to them.
Put into B1 (if your data start on A1):
Formula:
Please Login or Register to view this content.
accept with Ctrl+Shift+Enter
p.s. 10 characters shorter as formula len :-)
Last edited by KOKOSEK; 02-21-2019 at 08:58 AM.
Happy with my answer * Add Reputation.
If You are happy with solution, please use Thread tools and mark thread as SOLVED.
If there are always two letters W or F try this
Enter formula in B1 and copy down
Formula:Please Login or Register to view this content.
v A B 1 1F1 F1 2 12W2 W2 3 1000F12 F12 4 5000W51 W51
If you like my answer please click on * Add Reputation
Don't forget to mark threads as "Solved" if your problem has been resolved
"Nothing is so firmly believed as what we least know."
--Michel de Montaigne
It could be so easy, however.......Originally Posted by AlKey
Another one
=MID(A1,LEN(LOOKUP(,-LEFT(A1,ROW(A$1:A$99)))),99)
Thanks a Ton to Special-K, KOKOSEK, AlKey, Bo_Ry.
Since there are always two letters i.e. F & W in my data, applied AlKey formula as Array formula take longer time to process (since the formula to be applied to thousands of cells).
Anyhow thanks everyone.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks