Excel Help Please!

[Spuff]

I'm sure this is a fairly simple problem but unfortunately beyond my Excel skills

All I want to do is to sort my list of items in Box number order:

I have a number of stock items which are stored in boxes. The boxes are numbered and the items given a random number which makes up a unique SKU in the format:

B001/01234 where B001 is the box number and the bit after the / is a random number.

I want to list my boxes in order - easy enough - except in the early days I (stupidly) numbered my boxes in the format: 1234B1 (where the number after the B is the box number and then numbers before it are random).

Any thoughts please?

 

[Dale_tem]

Can you split the colummns using the / a B as a seperator

You can then use the & in the next cell to combine it all, ie

If cell B1 = 001 and cell B2 = 01234 you can put in B3 =B1&'/'&B2 and it will give you 001/01234

Or there might be another way of doing it.

 

[Longimanus]

You can have a separate column with just the box number.
=left(cell,4)
this returns the left 4 characters, you can then sort on that

(I think this is correct, not got excel at home to test)

 

[OutLore]

Completely misread the thread. Longimanus has it, but if the current format is:

1234B1 and B1 is the box number, you'll need =right(cell,2)

 

[Spuff]

Thanks all! Splitting the cell seems to have worked and I ended up using =right(cell,3). If I'd thought more carefully about the unique reference number when I set things up 5 years ago things would be much simpler today

 

[OutLore]

Thanks all! Splitting the cell seems to have worked and I ended up using =right(cell,3). If I'd thought more carefully about the unique reference number when I set things up 5 years ago things would be much simpler today

Just split the cells as you've done into different columns, so you have one column with all the random numbers, and one with the box numbers, then concatenate them together in the right order....
=concatenate(boxnumbercell,randomnumbercell)

Then make sure you keep it up to date in the new format moving forward