Need some help regarding formula to skip alphabet in alpha-numeric cells
Need some help regarding formula to skip alphabet in alpha-numeric cells
(OP)
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
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





RE: Need some help regarding formula to skip alphabet in alpha-numeric cells
RE: Need some help regarding formula to skip alphabet in alpha-numeric cells
If not, the blog post here:
https://newtonexcelbach.wordpress.com/2014/11/30/e...
has an open source VBA UDF that will extract all the numbers from a text string as values in separate cells.
Doug Jenkins
Interactive Design Services
http://newtonexcelbach.wordpress.com/
RE: Need some help regarding formula to skip alphabet in alpha-numeric cells
Is this true in all cases?
Skip,
Just traded in my OLD subtlety...
for a NUance!
RE: Need some help regarding formula to skip alphabet in alpha-numeric cells
TTFN (ta ta for now)
I can do absolutely anything. I'm an expert! https://www.youtube.com/watch?v=BKorP55Aqvg
FAQ731-376: Eng-Tips.com Forum Policies forum1529: Translation Assistance for Engineers Entire Forum list http://www.eng-tips.com/forumlist.cfm
RE: Need some help regarding formula to skip alphabet in alpha-numeric cells
Doing that will make it apparent why the results you get do not match your expectations.
RE: Need some help regarding formula to skip alphabet in alpha-numeric cells
I repeatedly saying, I am Design engineer and new to excel.
I am trying something to do in Excel for inspecting dimension values.
I evaluated the formula for upper value and came to know that if some character is before the value, it will print all the number and if not, it will print 1 digit less in the output cell.
Someone please help me out to solve this issue.
RE: Need some help regarding formula to skip alphabet in alpha-numeric cells
You could try responding to those who have provided useful information, rather than someone who didn't.
Did you look at the link I posted?
Does that do what you want? If not, what problems do you have with it?
Doug Jenkins
Interactive Design Services
http://newtonexcelbach.wordpress.com/
RE: Need some help regarding formula to skip alphabet in alpha-numeric cells
Your current requirement is 2 data values with a carriage return all in one cell, as an implied string. It would be better if both are defined as numbers for processing and the information is presented to the user as the string implies, not the other way around.
EDMS Australia
RE: Need some help regarding formula to skip alphabet in alpha-numeric cells
I have seen your post, I tried to implement here and not getting the output.
Getting compile error after using the formula =NumericOnly.
Thanks for your post and effort to solve my problem.
Regards,
Ravi
RE: Need some help regarding formula to skip alphabet in alpha-numeric cells
Where did you get = NumericOnly from? There is no function called NumericOnly
Look at the examples in rows 18 to 30 on the ExtractNums sheet. The formula:
=ExtractNums(A1) will extract all the numbers from a string in A1.
To see all the results:
- Enter the formula
- Select the cell with the formula, and as many cells to the right as you have numbers.
- Press Shift-Ctrl-R
Doug Jenkins
Interactive Design Services
http://newtonexcelbach.wordpress.com/
RE: Need some help regarding formula to skip alphabet in alpha-numeric cells
If so, then,
Major 2.0011
Minor 1.00234 R
2.0011 MID(A1,FIND("Major",A1)+6,FIND("Minor",A1)-(FIND("Major",A1)+6))
1.00234 MID(A1,FIND("Minor",A1)+6,FIND("R",A1,FIND("Minor",A1)+6)-(FIND("Minor",A1)+6))
If not, then the second part would be:
MID(A1,FIND("Minor",A1)+6,IF(ISNUMBER(FIND("R",A1,FIND("Minor",A1)+6)),FIND("R",A1,FIND("Minor",A1)+6)-(FIND("Minor",A1)+6),LEN(A1)+1-(FIND("Minor",A1)+6)))
TTFN (ta ta for now)
I can do absolutely anything. I'm an expert! https://www.youtube.com/watch?v=BKorP55Aqvg
FAQ731-376: Eng-Tips.com Forum Policies forum1529: Translation Assistance for Engineers Entire Forum list http://www.eng-tips.com/forumlist.cfm
RE: Need some help regarding formula to skip alphabet in alpha-numeric cells
Sorry for the late reply as I was on leave these days.
I tried ExtractNums, it was helpful.
Thank you very much for the support and your time.
Regards,
Ravi
RE: Need some help regarding formula to skip alphabet in alpha-numeric cells
But you came up with this formula?
CODE --> Excel
=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))))RE: Need some help regarding formula to skip alphabet in alpha-numeric cells
RE: Need some help regarding formula to skip alphabet in alpha-numeric cells
In that =ExtractNums function, for values like 1.230, 1.350 e.t.c, the output is 1.23 and 1.35.
Not able to get full decimals values including zero.
Can you please help on that?
RE: Need some help regarding formula to skip alphabet in alpha-numeric cells
Why stop at 3 decimal places? Especially when the two lonely examples you provided had only 2.
The point of the exercise was to extract a number from some text. If you want to format the resulting number in a particular way you can do this as a subsequent operation.
RE: Need some help regarding formula to skip alphabet in alpha-numeric cells
My inputs will be like 10.350 Dia, R 5.50 like that and I need to get output by removing text with 10.350 and 5.50 so on.
The output should be exactly with all numbers in input including zeros after decimal places as these values will be inspected for measuring components.
RE: Need some help regarding formula to skip alphabet in alpha-numeric cells
TTFN (ta ta for now)
I can do absolutely anything. I'm an expert! https://www.youtube.com/watch?v=BKorP55Aqvg
FAQ731-376: Eng-Tips.com Forum Policies forum1529: Translation Assistance for Engineers Entire Forum list http://www.eng-tips.com/forumlist.cfm
RE: Need some help regarding formula to skip alphabet in alpha-numeric cells
But if you must have the output displaying the same number of decimal places as entered, and that is variable, then you need to return the results as strings, rather than numbers.
So I have added that option to the extractnums function. See the example in the download file.
http://interactiveds.com.au/software/RegExpres.xls...
Doug Jenkins
Interactive Design Services
http://newtonexcelbach.wordpress.com/
RE: Need some help regarding formula to skip alphabet in alpha-numeric cells
See:
https://newtonexcelbach.wordpress.com/2017/03/11/e...
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/
RE: Need some help regarding formula to skip alphabet in alpha-numeric cells
Thank you very much for your support and time.
Really your post was very helpful.
your support is highly appreciated.
RE: Need some help regarding formula to skip alphabet in alpha-numeric cells
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/