Excel alphanumeric help - desperate!

Fiverqueen

I'm sure that the answer to this is really simple, but I'm completely stumped!

All I want to do is create a column that adds 2 to another column which contains letters and numbers - so if column A1 is 2a, I want column B1 to be 4a. My data only contains the numbers 2-7 and the letters a,b and c.

I have tried =SUM(A1+2) but this only works if I drop the letters - I really need the letters!

I have been Googling for hours but am clearly not looking for the right thing! Any help hugely appreciated!

boxst

Can you be more explicit with the format?

So you have:

2A
3B

(i.e. one number always on the left and one letter on the right?)

paddyrg

Split it into 2 parts, column A contains '2', B 'a', C contains the formula '=concatenate(A1, B1)', column D the formula '=concatenate ((A1 +2), B1)'

Columns C and D will now contain '2a' and '4a' accordingly

Fiverqueen

Yes - numbers are always this format, so either 2a, 2b, 2c, 3a,3b,3c etc up to 7. They all need 2 adding to them (they are students baseline grades and their predicted grades, which need to be 2 levels on).

Really appreciate any help you may have.

Tigsteroonie

Okay, so am presuming your have names in column A, and their baseline grade in column B; you want baseline grade + 2 in column C.

Here's my solution, but others may have a different view:

=((LEFT(B1,1)+2)&RIGHT(B1,1))

(translates as "take leftmost character of grade, add 2 to it, then take rightmost character of grade)

(aka. =CONCATENATE((LEFT(B1,1)+2),(RIGHT(B1,1))) but for some reason I never use the concatenate function, always the simple & instead!)

Fiverqueen

Thanks paddyrg - I can see how that would work, but I'd really like to keep to just the 2 columns if possible as this is part of a data sheet of 24 students with their levels from 12 modules - quite big already! (And I have gazillions of these sheets to do!).

It surely must be possible for excel to add to the number without changing the letter? Or am I asking the impossible?!!

Fiverqueen

Ooh, that looks hopeful Tigsteroonie - will give it a try.

Fiverqueen

Thank you so much Tigsteroonie! It worked, hurrah! This will save me hours! Phew!

:T:T:T

Tigsteroonie

Glad I could help

paddyrg

My methodology is to break it down into simpler steps which will cover all cases, so if you suddenly have a grade 11b, it won't fail, but if you are guaranteed only a single digit and single character then Tigs' answer is more compact.

As a data purist, what you are trying to do isn't "add 2 to a number with a suffix", but is actually to increment the value within a substring which looks like a number. 2a isn't a number, so it has to be handled as text, hence all the LEFT and RIGHT functions - it splits the 2a into 2 separate strings, Excel itself kindly handles the explicit type conversion from the "letter" 2 to the digit 2, so it can be treated as a number, then the suffix is nailed back onto the end of it. This is why it isn't as simple a job as you imagine it is under the covers!

EchoLocation

paddyrg wrote: »

My methodology is to break it down into simpler steps which will cover all cases, so if you suddenly have a grade 11b, it won't fail, but if you are guaranteed only a single digit and single character then Tigs' answer is more compact.

Good point, you should never be afraid to add additional columns to aid working and calculation.

Tigsteroonie's solution is the correct option. However, just FYI, should you ever get an initial number with more than a single integer eg. 11a, 115b, 5000c etc. then you could change the formula as follows..
=LEFT(A1,LEN(A1)-1)+2&RIGHT(A1,1)