• 100% Satisfaction Guarantee
The-PC-Guy, Software Engineer
Category: Programming
Satisfied Customers: 2050
Experience:  Extensive Knowledge in PHP, MYSQL, CSS & Javascript
62934938
The-PC-Guy is online now

# I have some cells with the format for example: SS4567 and some

### Resolved Question:

I have some cells with the format for example: SS4567 and some with the format: SX64. I need to retain the letters and the two numbers if that is all it has but if it has four, I need the first and third - so the first example would be converted to SS46.
Any ideas?
Submitted: 3 years ago.
Category: Programming
Expert:  The-PC-Guy replied 3 years ago.
The-PC-Guy :

My Name is***** will be helping you today. If I provide good service, you may ask for The-PC-Guy in your question title for your future needs.

The-PC-Guy :

is this in excel?

The-PC-Guy :

hello

The-PC-Guy :

is this in excel?

Expert:  The-PC-Guy replied 3 years ago.
chat does not seam to be workng

is this in excel?
Expert:  The-PC-Guy replied 3 years ago.
ok, assuming this is in excel and assuming that all cells you are using follow the same format, they have 2 lettters and either 2 or 4 numbers. then you would use this formula.

for now I will assume that cell A1 has your first value in it and will go from there.

if it is not in cell A1, just change every value of A1 in the formula to whatever cell you are referring to, and then you can copy it down whatever column you need to.

=IF(LEN(A1)=4,A1,LEFT(A1,2)&MID(A1,3,1)&MID(A1,5,1))

---------------------------------------------------------------------------------------------------------------

let me know if you have any questions, problems, or concerns

PLEASE DON'T FORGET TO RATE SO I AM PAID FOR MY TIME

IT WILL NOT COST ANYTHING ADDITIONAL BEYOND THE VALUE OF YOUR QUESTION

TO RATE, CLICK THE SMILEY FACES BENEATH THIS ANSWER

Do not rate negatively, instead continue the conversation with me so I can address any of your concerns

---------------------------------------------------------------------------------------------------------------

if you have any questions in the future you can visit my profile

and ask a question right in my box,

also you can put "PC Guy only" in your question title if you want to get to me.
Customer: replied 3 years ago.

Hi,

This seems to return 0 in all cases

Any other ideas?

Cheers

Stella

Expert:  The-PC-Guy replied 3 years ago.
can you send me an example sheet?
Expert:  The-PC-Guy replied 3 years ago.
also you may want to check the cell format of the target cell, make sure it is "general"