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

**cfriedrich** · 01-23-2013, 11:18 AM

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.

**Pauleyb** · 01-23-2013, 11:37 AM

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.

**bebo021999** · 01-23-2013, 11:41 AM

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

**Pete_UK** · 01-23-2013, 11:44 AM

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

**mnjofra** · 01-23-2013, 11:47 AM

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,

**cfriedrich** · 01-23-2013, 12:17 PM

Thanks very much for the prompt replies!

Originally Posted by Pauleyb

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'.

Originally Posted by bebo021999

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.

Originally Posted by Pete_UK

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.

Originally Posted by mnjofra

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.

**Pete_UK** · 01-23-2013, 01:04 PM

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

**cfriedrich** · 01-23-2013, 01:15 PM

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!

**FDibbins** · 01-23-2013, 01:31 PM

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??

**Pauleyb** · 01-23-2013, 01:32 PM

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)?

**cfriedrich** · 01-23-2013, 01:55 PM

Originally Posted by FDibbins

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!

Originally Posted by Pauleyb

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.

**Pauleyb** · 01-23-2013, 02:04 PM

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.

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

LinkBack

Thread Tools

Rate This Thread

Display

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

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

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

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

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

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

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

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

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

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

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

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

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions