Need some help regarding formula to skip alphabet in alpha-numeric cells 2

[raviprakash369]

Hi,
I need to print upper and lower values from alpha numeric cell by skipping alphabet.
Input will be like :
Major 2.00
Minor 1.00 R in a single cell ( 2.00 then alt+enter 1.00 R)

Output should be 2.00 and 1.00 in other 2 cells by skipping alphabet.

I am using the following formulae. But, have some issue with upper limit. some times I am getting less decimal value like if input is 1.0023, printing 1.002.

=LEFT(MID(MID(M324,1,FIND(CHAR(10),M324)),MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},MID(M324,1,FIND(CHAR(10),M324))&"0123456789")),MAX(SEARCH({0,1,2,3,4,5,6,7,8,9},"0123456789"&MID(M324,1,FIND(CHAR(10),M324))))),SUMPRODUCT(--ISNUMBER(--MID(MID(M324,1,FIND(CHAR(10),M324)),ROW(INDIRECT("1:"&(LEN(MID(M324,1,FIND(CHAR(10),M324)))))),2))))

=LEFT(MID(MID(M324,FIND(CHAR(10),M324),LEN(M324)-FIND(CHAR(10),M324)+1),MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},MID(M324,FIND(CHAR(10),M324),LEN(M324)-FIND(CHAR(10),M324)+1)&"0123456789")),MAX(SEARCH({0,1,2,3,4,5,6,7,8,9},"0123456789"&MID(M324,FIND(CHAR(10),M324),LEN(M324)-FIND(CHAR(10),M324)+1)))),SUMPRODUCT(--ISNUMBER(--MID(MID(M324,FIND(CHAR(10),M324),LEN(M324)-FIND(CHAR(10),M324)+1),ROW(INDIRECT("1:"&(LEN(MID(M324,FIND(CHAR(10),M324),LEN(M324)-FIND(CHAR(10),M324)+1))))),2))))

Where M324 is particular cell number.


If anyone knows how to solve, please help me.
If question is not clear, please let me know.

Thanks in advance.

Regards,
Ravi

 

[IDS]

Checking my ExtractNums function, I found it was only returning the last number as a string, the others were still returned as numbers even if the ReturnStrings argument was set to True. I have now fixed that, and also moved the returnstrings argument to be the first optional argument after the data range.

See:

https://newtonexcelbach.wordpress.com/2017/03/11/extracting-numbers-from-text-update/

for more details and download link.

The input to return the numbers as strings is now: =ExtractNums($A$18,TRUE)


Doug Jenkins
Interactive Design Services

http://newtonexcelbach.wordpress.com/

 

[raviprakash369]

Hi IDS,
Thank you very much for your support and time.
Really your post was very helpful.
your support is highly appreciated.

 

[IDS]

Glad it worked.

I would suggest you review the whole process though.
Can you get the original data input as values in separate cells in the first place?
Can you work with numbers, displayed to a fixed number of decimal places, rather than converting to strings?

Doug Jenkins
Interactive Design Services

http://newtonexcelbach.wordpress.com/