Hi all

I've done some googling on this, but I can't find anything. It's a bit vague and I'm not even really sure what to search on!

Without VB, just in Excel basic tools, is it possible to achieve the following?

I have data that is a series of ID numbers in blocks. Eg one block might look like this, and there are several blocks each with their own 5-digit prefix in B column:

A B
1 12345
2 123
3 1234
4 1234 B
5 12345

What I want to end up with is a 10-digit number for each, padded with zeroes in the middle, followed by any alpha character that belongs to it, with no spaces:

1234500123
1234501234
1234501234B
1234512345

I can't use LEN to determine whether to add '00' or '0' into the middle or not, because of the alphas mixed in.

I came up with this: =if(len(A2)=3,B$1&"00"&A2,if(len(A2)=4,B$1&"0"&A2,B$1&A2))

But the one in A4 ends up being "123451234 B", not "1234501234B" (zero in the middle - the space/nospace I can manage). There are also varying alphas on the various lists, from A to Z and including sometimes AA or AB. The alphas can be on any length suffix.

It's not absolutely essential that this isn't done in VB, but I don't know it very well at all, so if that's the only solution it would need to be quite clear what to do.

Any tips on how to achieve this, or even what I could actually search on that describes this issue to help myself more?

Thanks heaps
Deb