alterings cells automatically with one cell input

**stevesunfold** · 04-03-2008, 02:25 PM

hi guys

i have on sheet one a cell that requires an order number ( e.g something like j2345a ) what i would like to do on sheet 2 cell c10 i would like to extract the letter after the 4 figures so that what i would see in that cell is j2345.

what i should add is that the letter after the four numbers isnt always an "a" so it could be any letter that needs to be removed

i also need cell c10 on sheet 2 to remain empty if there is nothing entered in cell e8 of the first sheet

if anyone could help it would be greatly appreciated

thanks in advance

**Portuga** · 04-03-2008, 03:05 PM

Maybe this helps getting you closer to what you want:

the formula
=mid(A1,1,4) will return the first 4 characters of a cell. You can then drag down the formula.

**stevesunfold** · 04-03-2008, 03:21 PM

cant get that to work m8
should also have told you i have one formula in that cell already

=IF('GLASS SHEET'!E8="","",'GLASS SHEET'!E8)

is there anyway i can work round this mate??

thanks for your time

**ChemistB** · 04-03-2008, 03:47 PM

I'm assuming that GlassSheet!E8 is where your original Order number is.

Please Login or Register  to view this content.

This assumes your order number is always 6 characters long.

ChemistB

**Portuga** · 04-03-2008, 03:59 PM

What exactly doesnt work?

Formulas don´t affect the mid result. If its blank, the mid will return blank, if not blank, it will return the first 4 characters.

What is the value in E8?

**stevesunfold** · 04-03-2008, 04:00 PM

chemist that is spot on but being the complete idiot that i am i forgot to ask i f there was a way of doing if differently

8 times out of 10 the order number will be j2345a ( number changes for different jobs ) and the letter after is the times it has changed

ie

j2345 is the first
j2345a is the second
j2345b is the third

etc etc

if i have j2345b in the order cell i want the previous order which in this case is j2345a

sorry to be a real pain and i forgot to add that

can that be done??

**stevesunfold** · 04-03-2008, 04:37 PM

anyone please?

**stevesunfold** · 04-04-2008, 02:07 AM

unusual for you all to be stumped on here

**darkyam** · 04-04-2008, 08:11 AM

Perhaps

Please Login or Register  to view this content.

**ChemistB** · 04-04-2008, 09:42 AM

Okay, so if you have no letter, it stands as is. If it has an a, then it truncates the a, if it has a b, it becomes an a, if it's a c it becomes a b, etc?

Kinda tricky without VBA but I'll give it a try.

ChemistB

**ChemistB** · 04-04-2008, 10:18 AM

Based on my previous post, this is what you are looking for.

Please Login or Register  to view this content.

For those wanting a breakdown, the first IF says if E8 is empty, this cell is empty
The Second IF says if either the order number (in E8) is only 5 characters long OR if it ends in "a", then just put in the first 5 characters (truncate the "a")

IF that's not true (5 characters or ending in "a") then take the last character, convert it to it's ascii code (a number representing that letter) subtract 1 and then convert it back to a character.

Any questions?

ChemistB