A1 is 315
Obviously the smallest digit in cell A1 is "1"
So, in C1 what would the formula be to puck the 1 from the A1 3 digit cell and place in C1?
A1 is 315
Obviously the smallest digit in cell A1 is "1"
So, in C1 what would the formula be to puck the 1 from the A1 3 digit cell and place in C1?
a b c 1 613 1b1: =min(--mid(a1, {1,2,3}, 1)) 2 836 3 3 558 5 4 768 6
Entia non sunt multiplicanda sine necessitate
As the question was specific to 3-digit cells, shg's formula should suffice. If you (or anyone reading this) would like to be able to find the minimum value in a random-length numeric string, you could try the following array formula (must use CTRL+SHIFT+ENTER to confirm):
Formula:Please Login or Register to view this content.
shg's formula is for 3 digits only. For a cell with any number of digits use this array formula
=MIN(--MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))
Life's a spreadsheet, Excel!
Say thanks, Click *
Or you can put a vba function in your code module1 and use it like a worksheet function.
If value in cell A1 = 315 then in the cell where you want the value returned putPlease Login or Register to view this content.
Please Login or Register to view this content.
Any code provided by me should be tested on a copy or a mock up of your original data before applying it to the original. Some events in VBA cannot be reversed with the undo facility in Excel. If your original post is satisfied, please mark the thread as "Solved". To upload a file, see the banner at top of this page.
Just when I think I am smart, I learn something new!
Brilliant ideas above I am saving them.
I have to offer my simple solution though, if you have columns to spare,
just expand and do the min. That should get the work done if you are
not really into programming.
=MATCH(TRUE,ISNUMBER(FIND({0;1;2;3;4;5;6;7;8;9},A1)),0)-1
which handles any number of characters in A1, returns #N/A if there are no decimal numerals at all in A1, but ignores other characters when there's at least 1 decimal numeral in A1.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks