Copying part of cell to another by character reference?

[gredo33]

If in cell A1:A1000 I have Model names and serial numbers of items like this "Model Name : Serial Number". I would like to copy just the Serial Numbers into B1:B100.

I have tried a formula like =right (a1,5).

This works but the serial number isnt always 5 characters long. And there might be a few charachters after, which I dont need.

So could I just make a formula to copy the next 5 characters after the colon?

 

[acw]

Hi

Try

=MID(A1,SEARCH(":",A1)+1,5)

You may have to add 2 rather than 1 if you will always have a space after the colon.

Also, if the serial number isn't always 5 chars long, and there may be a few chars after, is there any way of identifying where the extra chars will start?


Tony

 

[stanleydgromjr]

Try this in cell 'B1':
=MID(A1,SEARCH(":",A1,1)+1,5)



Have a great day,
Stan

 

[gredo33]

Those work perfectly!

Thanks so much for the help.