+ Reply to Thread
Results 1 to 12 of 12

Multiplying cells with leading characters, and retaining those characters in the result.

  1. #1
    Registered User
    Join Date
    01-23-2013
    Location
    London
    MS-Off Ver
    Excel 2007
    Posts
    4

    Multiplying cells with leading characters, and retaining those characters in the result.

    Hi there,

    Forgive me if this has been asked before - no amount of Googling has led me to the answer!

    I have a spreadsheet where each cell is in the format of 2 letters followed by a numerical value, such as:

    AK25.75
    GN30
    BU.25
    ...

    I have to multiply these cells by a certain amount, so in the next column I have, for example:

    AK51.50
    GN60
    BU.50
    ...

    Is there a way to do this such that Excel ignores the text for the purpose of multiplication, but retains it in the next column? (There are 16 possible 2-letter combinations in the entire spreadsheet.)

    Furthermore, ideally I need to format any cells with a value greater than or equal to 48 as a factor of 48, where 'Y' is the letter used to signify the number 48. So, for the 2nd column, ideally it would read as follows:

    AKY3.50
    GNY12
    BU.50

    If anyone has any ideas, would hugely appreciate a hand with this! If anything's unclear, let me know and I'll try to better explain what I need to do. Many thanks.

  2. #2
    Forum Expert
    Join Date
    06-26-2010
    Location
    Austin, TX
    MS-Off Ver
    Excel 2010
    Posts
    1,671

    Re: Multiplying cells with leading characters, and retaining those characters in the resul

    Not sure how you handle something like GN100, is it just one Y or two Ys? I opted for two Ys. You can modify formula if needed.
    =LEFT(A2,2)&REPT("Y",INT(RIGHT(A2,LEN(A2)-2)*2)/48)&MOD(RIGHT(A2,LEN(A2)-2)*2,48)

    This is assuming your value is is A2 and you are multiplying it by 2 (per your example). Change that 2 to a cell reference if needed.
    Pauley
    --------
    If I helped with your issue, I'd appreciate a rep bump (hit the '*' icon to the bottom left of this post).

  3. #3
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    6,270

    Re: Multiplying cells with leading characters, and retaining those characters in the resul

    Why didnot BU.50 become BU.Y2, because 50>48?

  4. #4
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Excel 2007
    Posts
    19,653

    Re: Multiplying cells with leading characters, and retaining those characters in the resul

    Assuming those codes are in column A starting with A1, you could use this formula in B1:

    =LEFT(A1,2)&IF(RIGHT(A1,LEN(A1)-2)*2>48,"Y","")&MOD(RIGHT(A1,LEN(A1)-2)*2,48)

    and then copy down.

    Hope this helps.

    Pete

  5. #5
    Registered User
    Join Date
    08-22-2012
    Location
    Zagreb, Croatia
    MS-Off Ver
    Excel 2003
    Posts
    61

    Re: Multiplying cells with leading characters, and retaining those characters in the resul

    Hi,
    suposing that you have your example in A1:A3 and factor in E1 try this function in column B - B1 and copy to down:
    Please Login or Register  to view this content.
    Regards,
    If a post helps press star sign 4 my reputation

  6. #6
    Registered User
    Join Date
    01-23-2013
    Location
    London
    MS-Off Ver
    Excel 2007
    Posts
    4

    Re: Multiplying cells with leading characters, and retaining those characters in the resul

    Thanks very much for the prompt replies!

    Quote Originally Posted by Pauleyb View Post
    Not sure how you handle something like GN100, is it just one Y or two Ys? I opted for two Ys. You can modify formula if needed.
    =LEFT(A2,2)&REPT("Y",INT(RIGHT(A2,LEN(A2)-2)*2)/48)&MOD(RIGHT(A2,LEN(A2)-2)*2,48)

    This is assuming your value is is A2 and you are multiplying it by 2 (per your example). Change that 2 to a cell reference if needed.
    Ah, sorry, should have specified - for GN100 it would be GN2Y4, GN500 would be GN10Y20, etc.

    Your formula works perfectly (thanks) until I get to situations where I have to multiply figures such as 'GNY12', at which point it comes up with a VALUE error - presumably because of the inclusion of text in the form of the 'Y'.

    Quote Originally Posted by bebo021999 View Post
    Why didnot BU.50 become BU.Y2, because 50>48?
    Because by BU.50 I mean BU0.50 - we don't include a 0 before the decimal place for reasons of legibility, but it's not the end of the world if that has to change.

    Quote Originally Posted by Pete_UK View Post
    Assuming those codes are in column A starting with A1, you could use this formula in B1:

    =LEFT(A1,2)&IF(RIGHT(A1,LEN(A1)-2)*2>48,"Y","")&MOD(RIGHT(A1,LEN(A1)-2)*2,48)

    and then copy down.

    Hope this helps.

    Pete
    Thanks very much Pete - again, that seems to work perfectly until it comes to multiplying values including the Y variable, at which point I get a VALUE error once more.

    Quote Originally Posted by mnjofra View Post
    Hi,
    suposing that you have your example in A1:A3 and factor in E1 try this function in column B - B1 and copy to down:
    Please Login or Register  to view this content.
    Regards,
    Thanks for the response - this is spot-on, apart from not rendering values greater than 48 as Y, as mentioned in the second part of my post. This seems to be the main sticking point, so I'm wondering if it makes more sense to approach the issue from a different standpoint.

    If so, thanks to all your replies I think I understand how I could sort it so that in the 2nd column I'd have results like "AK51.50", "GN60", etc. - i.e. ignoring the complication of the 'Y' variable.

    Simplifying things, if we remove the leading characters from the equation, is there a formula just to render numbers greater than/equal to 48 using the 'Y' variable? So, just to turn '60' into Y12', '120' into '2Y24' and so on? In this case, I could split a cell like 'AK12' into 2 cells, one with the letters and the other with the numbers. A bit more work, but might make life easier!

    Thanks very much in advance.
    Last edited by cfriedrich; 01-23-2013 at 12:19 PM.

  7. #7
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Excel 2007
    Posts
    19,653

    Re: Multiplying cells with leading characters, and retaining those characters in the resul

    Do any of your 2-letter codes include a Y ?

    If not, then it would be possible to check for the existence of a Y and extract suffient characters. It might also be more consistent to include the number of Ys in all cases as a 2-digit number, so that you would have:

    AK00Ynnn, AK01Ynnn, AK02Ynnn, ... , AK10Ynnn

    and so on, where nnn is the numerical part.

    Would this be acceptable to you?

    Hope this helps.

    Pete

  8. #8
    Registered User
    Join Date
    01-23-2013
    Location
    London
    MS-Off Ver
    Excel 2007
    Posts
    4

    Re: Multiplying cells with leading characters, and retaining those characters in the resul

    Hi Pete,

    Unfortunately two of the codes (YE and CY) do contain the letter Y. In terms of having 00Y, 001Y, etc. that wouldn't be ideal unfortunately - don't mean to be difficult, I should probably explain that these codes refer to a paint pigmentation system, so the two-letter codes are the pigments and the numbers are the number of shots of each pigment to be dispensed.

    For some reason, several manufacturers use a system whereby 48 shots of pigment is referred to as 'Y', hence the issue. Having 00Y would be somewhat confusing, therefore.

    As I say, it's really not an issue if I have to split the cells up into the separate letter and number components, which would seem to simplify matters. Would this make it any easier to convert values such as '100' shots into '2Y4' shots?

    Really appreciate your help!

  9. #9
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    49,992

    Re: Multiplying cells with leading characters, and retaining those characters in the resul

    seems to me you keep changing the goal posts here?
    I have a spreadsheet where each cell is in the format of 2 letters followed by a numerical value,
    AK25.75
    GN30
    BU.25
    but then you say...
    Furthermore, ideally I need to format any cells with a value greater than or equal to 48 as a factor of 48, where 'Y' is the letter used to signify the number 48. So, for the 2nd column, ideally it would read as follows:

    AKY3.50
    GNY12
    BU.50
    and then in post 6...
    Ah, sorry, should have specified - for GN100 it would be GN2Y4, GN500 would be GN10Y20, etc.
    so... is it 2 1st letters, 3 1st letters, 2 1st letters with another letter in 4th place??
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  10. #10
    Forum Expert
    Join Date
    06-26-2010
    Location
    Austin, TX
    MS-Off Ver
    Excel 2010
    Posts
    1,671

    Re: Multiplying cells with leading characters, and retaining those characters in the resul

    A few thoughts:
    There is still some ambiguity in your request, as far as I can tell.
    For example, what is the result you want if your 'input' is GBY25 and you multiply it by 2? I assume it would be GB3Y2.

    Because of the situation above, I'm not sure how separating the input to separate letter and number components would help. How would something like AN3Y12 be split? Three columns (AN, 3Y, 12) or two columns (AN, 3Y12) or (AN3Y, 12)?

    I might work on this a bit over lunch, but would like some clarification on your expected results. Also, are you ever going to multiply by a number < 1 (i.e. divide)?

  11. #11
    Registered User
    Join Date
    01-23-2013
    Location
    London
    MS-Off Ver
    Excel 2007
    Posts
    4

    Re: Multiplying cells with leading characters, and retaining those characters in the resul

    Quote Originally Posted by FDibbins View Post
    seems to me you keep changing the goal posts here?


    but then you say...


    and then in post 6...


    so... is it 2 1st letters, 3 1st letters, 2 1st letters with another letter in 4th place??
    My apologies if my phrasing has been unclear. It is always one of 16 2-letter combinations to begin with, followed by a numerical value. In instances where the numerical value is greater than/equal to 48 and less than 96, however, it would be 2 letters followed by a third 'letter' (Y). This third 'letter' is actually a representation of the numerical value (i.e. 48), however - Y is a variable, like in the equation 2x^2.

    So, the following are the possible locations of the 'Y' (3rd, 4th and 5th places):

    GNY2
    GN2Y2
    GN12Y2

    There would never be a paint recipe where there were 3 digits preceding the Y. Does that make any more sense? Sorry to cause any confusion!

    Quote Originally Posted by Pauleyb View Post
    A few thoughts:
    There is still some ambiguity in your request, as far as I can tell.
    For example, what is the result you want if your 'input' is GBY25 and you multiply it by 2? I assume it would be GB3Y2.

    Because of the situation above, I'm not sure how separating the input to separate letter and number components would help. How would something like AN3Y12 be split? Three columns (AN, 3Y, 12) or two columns (AN, 3Y12) or (AN3Y, 12)?

    I might work on this a bit over lunch, but would like some clarification on your expected results. Also, are you ever going to multiply by a number < 1 (i.e. divide)?
    Exactly, yes - GBY25 would mean GB(48+25), or GB73. So, multiplying it by 2 would result in GB146, which works out at GB((3x48)+2), or GB3Y2.

    Something like AN3Y12 would be split into two columns - AN and 3Y12. The two starting letters are completely separate - in context, they refer to the pigment to be dispensed, whilst the 3Y12 refers to the number of shots of that pigment to be dispensed (156).

    I assumed splitting the cells would be simpler on the basis that it removes the need to ignore the 2 starting letters when multiplying the (Y-coded) numerical value. By which I mean that as far as I can tell, in that scenario all that would be needed is a way to make Excel read 'Y' as signifying 48.

    There would never be any division, nope. Again, thank you for your advice.

  12. #12
    Forum Expert
    Join Date
    06-26-2010
    Location
    Austin, TX
    MS-Off Ver
    Excel 2010
    Posts
    1,671

    Re: Multiplying cells with leading characters, and retaining those characters in the resul

    I went with multiple columns to keep it quick and dirty. Combining this all into one formula (or finding a better solution) "is an exercise left to the reader."
    A1: Your Input
    B1: =LEFT(A1,2)
    C1: =IF(MID(A1,3,1)="Y",1,IFERROR(MID(A1,3,FIND("Y",RIGHT(A1,LEN(A1)-2))-1),0))
    D1: =IFERROR(MID(A1,1+FIND("Y",A1,3),500),MID(A1,3,1000))
    E1: Your Multiplier
    F1: =E1*(48*C1+D1)
    G1: =B1&IF(F1>=48,INT(F1/48)&"Y","")&MOD(F1,48)

    G1 holds your answer.
    Last edited by Pauleyb; 01-23-2013 at 02:12 PM. Reason: Changed C1 for case of no # before Y

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1