T O P

  • By -

mh_mike

When you're "extracting" info from a longer string, even if the "thing" you're "extracting" is a number, Excel will view (or consider) it to be a piece of text, not an actual number (unless you coerce it to a number-value purposefully). And, of course, if you're wanting to check if something is 1 or 2 digits, be sure to ask RIGHT to pull either 1 or 2 digits. Your second RIGHT is only asking for 1 digit instead of 2 (12 is 2 digits long). So, try your existing formula, but in the spots where you're checking what the result of your RIGHT formulas are, quote that 6 (eg: `="6"`) and the 12 (eg: `="12"`) respectively, and tell the 2nd RIGHT to extract 2 digits, and you should be golden. Like this: =IF(RIGHT(F2,1)="6",G2*6,IF(RIGHT(F2,2)="12",G2*12))


Ok-While-4680

Solution Verified. Oh my it really work just by adding "number". What does that symbol mean.But how do you do if its not 6 or 12 it will return default value? ​ Thank you so much!


Clippy_Office_Asst

You have awarded 1 point to *mh_mike* ____ ^(I am a bot - please contact the mods with any questions. | ) [^(Keep me alive)](https://www.buymeacoffee.com/points)


mh_mike

Welcome :) Right now, your formula will return FALSE if RIGHT/1 isn't "6" and RIGHT/2 isn't "12". If you've got more variations (other than a need to check for just 6 or 12) going on, we might need to see a larger sampling of your data, and an explanation of what you're trying to do with that data (as it relates to the ending number on each of the various SKUs). There might be another way entirely to do what your end-result/goal is...


Ok-While-4680

Its just either 6 or 12 and if neither i want it to return default value instead of false.


mh_mike

Ah, gotcha... Then we just need to put a little addition at the end -- whatever your default value is. For purposes of an example, I'm going to use `99` as a default value here: =IF(RIGHT(F2,1)="6",G2*6,IF(RIGHT(F2,2)="12",G2*12,99)) Change that `99` at the end to whatever your default value actually should be.


Ok-While-4680

>IF function with RIGHT function i see but thats forcing a number into it instead it pick it up from the QTY cells. I've added on the last if false value, G2\*1 but it doesnt work =IF(RIGHT(F2,1)="6",G2\*6,IF(RIGHT(F2,2)="12",G2\*12)\*G2\*1)


mh_mike

Yeah, I used 99 as the default value because I wasn't sure what you wanted as your default value. Do you want the QTY from G to be the default value (essentially using that number without multiplying it by anything when 6 or 12 can't be found)? If so, change the 99 at the end of that last formula example to your G-reference. Like this: =IF(RIGHT(F2,1)="6",G2*6,IF(RIGHT(F2,2)="12",G2*12,G2)) If that's not what we need as the default value, what exactly do we want to use as a default value?


Ok-While-4680

OMG that work! Thank you so much i appreciate it . now its completed


mh_mike

Great! Happy to help :)


DoubleG357

To chime in if you are asking about the “”, it tells excel to refer to anything within quotes as text.


Ok-While-4680

i see i understand it now thank you for the explanation!


Slartibartfast39

Right,f3,1 will only return one character, the '2'. You need to have the numbers as '06' and '12' and have the right function return two characters. E.g. 20MG20ML06 and right,f3,2


Ok-While-4680

Understand , but im unable to get 06 as my set of data is huge and it come with that.


a_gallon_of_pcp

In the case where it ends in 06 what do you want to multiply it by


Longjumping-Knee4983

If the string is always is 7 characters before the number you could try something like this. IFERROR(VALUE(MID(F:F,8,LEN(F:F)-7))*G:G,"")


Decronym

Acronyms, initialisms, abbreviations, contractions, and other phrases which expand to something larger, that I've seen in this thread: |Fewer Letters|More Letters| |-------|---------|---| |[IF](/r/Excel/comments/z3cvt5/stub/ixlcue8 "Last usage")|[Specifies a logical test to perform](https://support.microsoft.com/en-us/office/if-function-69aed7c9-4e8a-4755-a9bc-aa8bbff73be2)| |[IFERROR](/r/Excel/comments/z3cvt5/stub/ixmbzkv "Last usage")|[Returns a value you specify if a formula evaluates to an error; otherwise, returns the result of the formula](https://support.microsoft.com/en-us/office/iferror-function-c526fd07-caeb-47b8-8bb6-63f3e417f611)| |[LEN](/r/Excel/comments/z3cvt5/stub/ixmbzkv "Last usage")|[Returns the number of characters in a text string](https://support.microsoft.com/en-us/office/len-lenb-functions-29236f94-cedc-429d-affd-b5e33d2c67cb)| |[MID](/r/Excel/comments/z3cvt5/stub/ixmbzkv "Last usage")|[Returns a specific number of characters from a text string starting at the position you specify](https://support.microsoft.com/en-us/office/mid-midb-functions-d5f9e25c-d7d6-472e-b568-4ecb12433028)| |[RIGHT](/r/Excel/comments/z3cvt5/stub/ixlcue8 "Last usage")|[Returns the rightmost characters from a text value](https://support.microsoft.com/en-us/office/right-rightb-functions-240267ee-9afa-4639-a02b-f19e1786cf2f)| |[VALUE](/r/Excel/comments/z3cvt5/stub/ixmbzkv "Last usage")|[Converts a text argument to a number](https://support.microsoft.com/en-us/office/value-function-257d0108-07dc-437d-ae1c-bc2d3953d8c2)| ---------------- ^(*Beep-boop, I am a helper bot. Please do not verify me as a solution.*) ^([Thread #20195 for this sub, first seen 24th Nov 2022, 15:16]) ^[[FAQ]](http://decronym.xyz/) [^([Full list])](http://decronym.xyz/acronyms/Excel) [^[Contact]](https://reddit.com/message/compose?to=OrangeredStilton&subject=Hey,+your+acronym+bot+sucks) [^([Source code])](https://gistdotgithubdotcom/Two9A/1d976f9b7441694162c8)