In cells A1, B1, C1 I have codes like P30K, 36L, P38WT.,
I am looking to find the sum of the #s in each cell. (104)
What is the easiest way to do this. Note Sometimes there are 2 letters before or after the Number, sometimes there is one, or none.
Thanks
Last edited by PaddyP; 08-17-2016 at 11:49 AM.
There are lots of ways to do this. here's one:
=LOOKUP(99^99,--(0&MID(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&1234567890)),ROW(INDIRECT("1:"&LEN(A1)+1)))))
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.
Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh
Is it always a 2 digit number or will it vary?
Biff
Microsoft MVP Excel
Keep It Simple Stupid
Let's Go Pens. We Want The Cup.
Thank you Glenn!
Always 2 digit Tony.
If there are only ever 2...
=LOOKUP(99^99,--(0&MID(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&1234567890)),2)))
Glad to have helped...
Like this...Originally Posted by PaddyP
Data Range
This formula entered in A2 and copied across...
=--MID(A1,MIN(FIND({1,2,3,4,5,6,7,8,9,0},A1&1234567890)),2)
Then you can do a SUM in D2...
=SUM(A2:C2)
Last edited by Tony Valko; 08-17-2016 at 12:00 PM.
Thanks Tony that is also a great way to do it!
You're welcome. Thanks for the feedback!
or this!
Formula:
Please Login or Register to view this content.
v A B C D E F 1 P30K 36L P38WT 30 36 38
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
You're making an assumption that these are clothing sizes and that the first digit will never exceed 5?? Even in the USA???
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks