How JustAnswer Works:
  • Ask an Expert
    Experts are full of valuable knowledge and are ready to help with any question. Credentials confirmed by a Fortune 500 verification firm.
  • Get a Professional Answer
    Via email, text message, or notification as you wait on our site.
    Ask follow up questions if you need to.
  • 100% Satisfaction Guarantee
    Rate the answer you receive.
Ask The-PC-Guy Your Own Question
The-PC-Guy
The-PC-Guy, Software Engineer
Category: Programming
Satisfied Customers: 1997
Experience:  Extensive Knowledge in PHP, MYSQL, CSS & Javascript
62934938
Type Your Programming Question Here...
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: 2 years ago.
Category: Programming
Expert:  The-PC-Guy replied 2 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 2 years ago.
chat does not seam to be workng

is this in excel?
Expert:  The-PC-Guy replied 2 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

http://www.justanswer.com/computer/expert-1expert-2rulethemall/

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 2 years ago.

Hi,

This seems to return 0 in all cases

Any other ideas?

Cheers

Stella

Expert:  The-PC-Guy replied 2 years ago.
can you send me an example sheet?
Expert:  The-PC-Guy replied 2 years ago.
also you may want to check the cell format of the target cell, make sure it is "general"
The-PC-Guy and other Programming Specialists are ready to help you
Customer: replied 2 years ago.

Genius!

Very many thanks

Stella