Create a uniform drop down list

[ipon70]

Hello,
I am looking to try and make all of the text line up better so that the drop downs looks more uniform.
Here is an example of what my issue is. All the lines by the first straight line start at different spots and makes it hard to read in a drop down.
The below data is from a concatenated list that will change at any moment in time.

BAILEY, ANGEL A | PHY-F | PAH | TLOD NO | HY YES | QRE YES

THOMISON, MATTHEW S | SPHY-M | PAH | TOLD YES | HY NO | QRE YES

MALKOW-DUNHAM, TAMMIE A | SPHY-F | PAH | TOLD NO | HY NO | QRE NO

JACOBSON II, MICHAEL G | PHY-M | PAH | TOLD YES | HY YES | QRE YES

CAVANAUGH, BRITTANY C | LPHY-F | PAL | TOLD NO | HY NO | QRE YES

What I would like it to look like, more like a straight line for the start and maybe the others also. Doesn't need this much space but you get the idea.

BAILEY, ANGEL A	| PHY-F	| PAH	| TLOD NO	| HY YES	| QRE YES
THOMISON, MATTHEW SS	| SPHY-M	| PAH	| TOLD YE	| HY NO	| QRE YES
MALKOW-DUNHAM, TAMMIE A	| SPHY-F	| PAH	| TOLD NO	| HY NO	| QRE NO
JACOBSON II, MICHAEL G	| PHY-M	| PAH	| TOLD YES	| HY YES	| QRE YES
CAVANAUGH, BRITTANY C	| LPHY-F	| PAL	| TOLD NO	| HY NO	| QRE YES

Hopefully that makes sense.

Thanks in advance

 

[fjns]

Hi, try the TEXTSPLIT function...


TEXTSPLIT function

 

[fjns]

Hi, try the TEXTSPLIT function...


TEXTSPLIT function

Textsplit.xlsx

 

[ipon70]

Hi, try the TEXTSPLIT function...


TEXTSPLIT function

Let me check this out and see

 

[ipon70]

Textsplit.xlsx

Ohhhh sorry no, I don't want it split I just want it lined up but all in one column, like controlling the layout....so like if one name is 10 characters long and the next one is 14, then none of the lines in either case would start until 30...so the name with 10 characters would have 20 space after it to equal 30 and the 15 character name would have 16, to add up to 30. I am trying to make the drop down have a uniform look where all the lines start very close to the same location so your eyes can read it easier.

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

If you are talking about a Data Validation list then it's probably not possible as you would need to change the font & I'm not sure that's possible anymore.

 

[ipon70]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

If you are talking about a Data Validation list then it's probably not possible as you would need to change the font & I'm not sure that's possible anymore.

I will do that right now. No its not the data validation, its a list that is being brought together into one column from 6 columns, but after the first column is concatenated, I want to start the next section at a set distance out...so that the first line shows lined up with the next line below it and so on.

 

[Fluff]

In that case what is your raw data like & what formula are you currently using?

 

[ipon70]

In that case what is your raw data like & what formula are you currently using?

The raw data starts as a csv file on the import page. It is then brought over to the page I am working on with a simple =If(MS!A1="","",MS!A1) type formula.
Then a concatenated like this. =IF(B2="","",CONCATENATE(Z2," | ",Q2," | ",R2," | ","TLOD ",S2," | ","HY ",T2," | ",IF(U2="","QRE NO","QRE "&U2)," | "))
So what I am trying to do is right after the "Z2" create exactly the same amount of space regardless of name length which I have determined is 30 spaces. So on the 31 space should be the " | ", and everything after.

 

[Fluff]

Ok, how about

=IF(B2="","",CONCATENATE(Z2&REPT(" ",30-LEN(Z2))," | ",Q2," | ",R2," | ","TLOD ",S2," | ","HY ",T2," | ",IF(U2="","QRE NO","QRE "&U2)," | "))

and change the font to a mono-spaced font like courier.