LinkBack URL
About LinkBacks
I have this on a1 and a2
078ABR39
1096DGN41
I want result
078
1096
I know the LEFT(A1,3) and LEFT (A1,4)
but i don't to waste time edit where applicable
I have this on a1 and a2
078ABR39
1096DGN41
I want result
078
1096
I know the LEFT(A1,3) and LEFT (A1,4)
but i don't to waste time edit where applicable
Formula on B1 to drag down
B1=LEFT(A1,MATCH(FALSE,ISNUMBER(1*MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)),0)-1) use ctrl+Shift+Enter.
Formula:
Please Login or Register to view this content.
If I helped, Don't forget to add reputation (click on the little star ★ at bottom of this post)
Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
A non-array alternative
=LOOKUP(99^9,LEFT(A2,ROW($1:$10))+0,LEFT(A2,ROW($1:$10)))
if you have VERY long (>10 character) numbers, increase the $10 in the above to whatever you need
Last edited by Glenn Kennedy; 09-09-2015 at 02:14 AM.
Glenn
None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU
But one which is not so rigorous. For example, with:Originally Posted by Glenn Kennedy
07APR399
in A2, your formula - given the appropriate language/date settings - returns:
07APR39
not 07, as desired.
Regards
Last edited by XOR LX; 09-09-2015 at 02:17 AM.
Thank you works , picked the array as Glenn yours works for first 2 rows on the following data
078ABR39
078AC39
0988BR36
0988BR37
0988BR38
0988BR39
0988BR40
0988C36
0988C37
Here's another one.
Data Range
This formula entered in B1 and copied down:
=LEFT(A1,SUMPRODUCT(--ISNUMBER(-MID(A1,ROW(INDIRECT("1:5")),1))))
Assumes the longest number string is 5 digits. If it might be longer then adjust this portion of the formula to suit:
INDIRECT("1:5")
Biff
Microsoft MVP Excel
Keep It Simple Stupid
Let's Go Pens. We Want The Cup.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks