Fixed string length

**Madball** · 12-01-2009, 05:51 AM

Hello

I am trying to create a string of text that grabs info from other cells, which is easy so for example

=A1&A2&A3

would grab all the info from the 3 cells and merge them to create a string, but what I want to do is create a prefixed length.

Lets say A1, A2 and A3 would equal 10 characters in total but B1, B2 and B3 would equal 7 characters, these would be different lengths. so for a visual description I would like them to appear like this

At the moment

01MAIN-HELLO
01DESSERT-HELLO WORLD

Would like

01MAINXXX-HELLO
01DESSERT-HELLO WORLD

This would make both strings the same length and would make it easier for me to export to a custom file that needs a certain length prefix.

What formula do I use for that?

**teylyn** · 12-01-2009, 06:09 AM

Your description in the text does not match the example provided. This is confusing. What exactly is in A1 A2 A3. Can you upload a workbook with before and after examples and explain the logic.

**teylyn** · 12-01-2009, 06:14 AM

... pending the arrival of more detailed specs, maybe something along the lines of

=IF(LEN(A1)<10,A1&REPT("x",10-LEN(A1)),LEFT(A1,10))&"_"&IF(LEN(B1)<10,B1&REPT("x",10-LEN(B1)),LEFT(B1,10))

adjust red bits to suit.

**DonkeyOte** · 12-01-2009, 06:17 AM

We can see that your fixed len is 15 chars but it might be worth outlining how you wish to break that out in terms of sub section eg:

row 1 should be 2 chars (00 number format)
row 2 should be 7 chars
row 3 should be 6 chars including hyphen

if that's the case then you can use something like

Please Login or Register  to view this content.

**Madball** · 12-01-2009, 06:18 AM

Okay I do apologise,

lets say in in cell A2 I have this formula

Please Login or Register  to view this content.

Within B2 there would be a number between 01 to 09
Within C2 there would be any 1 word text you want
Within D2 there would be a dash ( - )
Within E2 there would be any text at all and at any length.

So for example within A2 and using the formula above it could show something like this

01MAIN-HELLO

or

01DESSERT-HELLO WORLD

As you can see the string before the dash in both examples are two different lengths, what I would like it to do is make the string before the dash a fixed length. So no matter what what you put into cell C2 it would always display a fixed number of characters.

so it would look like this

01MAINXXX-

and

01DESSERT-

As you can see 01MAINXXX is the same length as 01DESSERT-

Not sure if this makes sense, but I am trying to find if there is a fomula for this or is it a case of using Custom within cell formatting.

**pike** · 12-01-2009, 06:29 AM

you could use a UDF

Please Login or Register  to view this content.

**Madball** · 12-01-2009, 06:37 AM

Sorry I am a bit confused, I have never used UDF before? Is that Visual Basic stuff or is that code just dropped straight into a cell?

**Madball** · 12-01-2009, 06:50 AM

Originally Posted by DonkeyOte

We can see that your fixed len is 15 chars but it might be worth outlining how you wish to break that out in terms of sub section eg:

row 1 should be 2 chars (00 number format)
row 2 should be 7 chars
row 3 should be 6 chars including hyphen

if that's the case then you can use something like

Please Login or Register  to view this content.

After trying this out it is starting to work for me, the only issue I see with this is that no matter what I type into cell A2 it always leaves a certain amount of XXX after it

So it would look like this:

MAINXXXXXXXX
DESSERTXXXXXXX

If I want it to become the same I am trying to find out which part of the formula I need to change so it would look like this instead

MAINXXX
DESSERT

As these are the same length

**DonkeyOte** · 12-01-2009, 06:57 AM

Madball, I think people are wary of getting involved here because the logic appears to fluctuate and we could end up in a twenty questions scenario

Earlier you stated:

Originally Posted by Madball

This would make both strings the same length and would make it easier for me to export to a custom file that needs a certain length prefix.

However you've yet to specify what that length is - the implication at present is that the length of row 1 string is always 2, the length of row 2 string should be determined by max string length of A2:B2 and the length of row 3 string is irrelevant...
none of which would imply you have a prefix length for your file given the length is in essence random (and the pre hyphen string length is variable based on longest word in row 2 - there is seemingly no cap)

You will need to clarify all of the above before people will feel comfortable composing a "winning" formula... as is we're guessing (at best).

**estige** · 12-01-2009, 07:49 AM

Is your goal to make the dashes appear in the same position? And varying lenght after the dash is no problem?
If that is the case, maybe something like this?

A2:

Please Login or Register  to view this content.

You can also use a formula to check what is the maximum lenght of the word between the number and the dash.

Please Login or Register  to view this content.

(if your matrix is in row 2 to 14)
Must be entered as an array, that is with ctrl/shift/enter

Then you can replace "7" in the first formula with a reference to this cell.