Concatenate with varying lengths and data

**Unident** · 05-20-2013, 06:30 PM

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

**martindwilson** · 05-20-2013, 06:55 PM

as long as there is a space

Formula:

Please Login or Register  to view this content.

if spaces or not

Formula:

Please Login or Register  to view this content.

**Unident** · 05-20-2013, 06:58 PM

Thank you, thank you, thank you!!

Works perfectly.

I think I'm going to love this place.

**arlu1201** · 05-21-2013, 01:57 AM

Based on your last post in this thread, its apparent that you are satisfied with the solution(s) you've received and have solved your question, but you haven't marked your thread as "SOLVED". I will do it for you this time.

In future, to mark your thread as Solved, you can do the following -
Select Thread Tools-> Mark thread as Solved.

Incase your issue is not solved, you can undo it as follows -
Select Thread Tools-> Mark thread as Unsolved.

Also, since you are relatively new to the forum, i would like to inform you that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post which helped you. This adds to the reputation of the person who has taken the time to help you.