Eng-Tips is the largest engineering community on the Internet

Intelligent Work Forums for Engineering Professionals

Register Log in

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

Status
Not open for further replies.
raviprakash369

raviprakash369

Aerospace
Mar 25, 2015
49
0
0
IN
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


 
Replies continue below

Recommended for you

Sort by date Sort by votes
I'd use VBA to write a User Defined Array Function for this.[ ] Use VBA's LEFT(), RIGHT() and MID() functions to extract the two numeric substrings out of the single large string, then use its CDBL() function to convert each substring to the corresponding numerical value.[ ] Return the two values in a 1x2 array.[ ] You'll need to detect and handle errors.[ ] You'll also need to think about what sorts of numeric substrings might be embedded in your single large string.
 
Upvote 0 Downvote
"Input will be like" indicates that there is a SPACE or LINEFEED character between "words" and each "word" will be either an alpha "word" or a numeric "word."

Is this true in all cases?

Skip,
[sub]
[glasses]Just traded in my OLD subtlety...
for a NUance![tongue][/sub]
 
Upvote 0 Downvote
Please explain how the formulas that you presented work.

Doing that will make it apparent why the results you get do not match your expectations.
 
Upvote 0 Downvote
Dear IRStuff,
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.
 
Upvote 0 Downvote
I'd be looking at what data needs to be stored and how its going to provide the functions you need, instead of allocating it as a string and then attempting to process it into something useful.

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
 
Upvote 0 Downvote
Hi IDS,
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
 
Upvote 0 Downvote
Getting compile error after using the formula =NumericOnly
Click to expand...

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
 
Upvote 0 Downvote
Does it always end in "R?"

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! faq731-376 forum1529 Entire Forum list
 
Upvote 0 Downvote
HI IDS,
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
 
Upvote 0 Downvote
Ravi said:
I repeatedly saying, I am Design engineer and new to excel.
Click to expand...

But you came up with this formula?
Code: 
=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))))

 
Upvote 0 Downvote
Hi IDS,
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?
 
Upvote 0 Downvote
The FULL decimal values would be 1.2300000000000000000000… … …
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.
 
Upvote 0 Downvote
Hi Denial,
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.

 
Upvote 0 Downvote
Ravi - if you want to display 3 decimal places, then the simplest and best way to do it is to format the output to display 3 decimal places.

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.




Doug Jenkins
Interactive Design Services
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

Kenja824
  • Locked
  • Question
Im looking for some formula help please --- Again. lol
2
Replies
37
Views
38
Kenja824
Kenja824
dik
  • Locked
  • Question
Copying Cells 2
Replies
6
Views
19
dik
dik
Kenja824
  • Locked
  • Question
New Formula Help 1
Replies
3
Views
14
SkipVought
SkipVought
Kenja824
  • Locked
  • Question
Formula help please
Replies
11
Views
20
SkipVought
SkipVought
Kenja824
  • Locked
  • Question
excel Formula Find help
Replies
5
Views
24
Kenja824
Kenja824
Back
Top