removing letters from numbers and letters combo.xlsx
Anyone able to put a formula in column C?
Basically want the letters out of VAT Numbers, in our database, some have the country letters at the beginning and some don't.. Need them all as the numbers only for intrastats
Try this formula
=MID(A2,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A2&"0123456789")),SUM(LEN(A2)-LEN(SUBSTITUTE(A2,{0,1,2,3,4,5,6,7,8,9},""))))
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
=sumproduct(mid(0&a2,large(index(isnumber(--mid(a2,row($1:$25),1))*
row($1:$25),0),row($1:$25))+1,1)*10^row($1:$25)/10)
If you arehappy with the results, please add to the contributor's
reputation by clicking the reputation icon (star icon).
If you are satisfied with the solution(s) provided, please mark your thread as Solved.
Select Thread Tools-> Mark thread as Solved.
To undo, select Thread Tools-> Mark thread as Unsolved.
http://www.excelaris.co.uk
AFAIK - if there are letters, it is always 2-leter, so simple formula would be:
=IF(CODE(A2)>=65,1*MID(A2,3,100),A2)
1* is used to convert text into number and 100 is used to be much too long to cover any length of VAT number
Best Regards,
Kaper
ThanksOriginally Posted by AlKey
Cheers to the other two also, as always....a million ways to skin a cat in Excel.
You're welcome and thank you for your feedback!
Please mark thread as "Solved" if your issue has been resolved. (Selecting Thread Tools-> Mark thread as Solved).
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks