Aligning text in a cell with respect to character width and spacing

[JackDanIce]

Hi,

I have a column of data with strings xxx | yyyyyyy [zzz]

Where xxx is a unique 3 or 4 digit code, yyyyyyy is a string (of variable length) and zzz is a 3 digit (non unique) code
| and [] are effectively separator characters.

Current look:

AIH | Airpower Corporate [AUT]

AIHT | Airpower Corporate - Operational [BEL]

AIR | Anchor Butter Airpower NV Air [BAB]

How can I change the cell format (or other method that doesn't require manual editing) so all the | are aligned vertically down the column?

TIA,
Jack

 

[JackDanIce]

Formula for said column:

=[@[Operation_Unit_Code]]&" | "&[@[Operation_Unit_Name]]&" [" &[@[Country_Code_(3CHR)]]&"]"

Adjusted to, which slightly improves, but same issue remains:

=[@[Operation_Unit_Code]]&REPT(" ",4-LEN([@[Operation_Unit_Code]]))&" | "&[@[Operation_Unit_Name]]&" [" &[@[Country_Code_(3CHR)]]&"]"

 

[footoo]

In another column : =IF(FIND("|",A1)=4," " & A1, A1)
Format the column with a monotype font and align left.

Or maybe you prefer :
=IF(FIND("|",A1)=4,LEFT(A1,3) & " " & RIGHT(A1),LEN(A1)-3),A1)

 

[J.Ty.]

I strongly recommend brining the data to First Normal Form, by running Ribbon->Data->Data tools->Text to Columns and choose "|" and "[" a s spearators, later eliminate "]" by Ctrl-H (search and replace).

First NF is always advisable, saves a lot of headache in designing formulas. Besides that, normal cell formating tools may be used to align everything.

J.Ty.

 

[footoo]

NOTE : If there is a space before | , the formula should be : =IF(FIND("|",A1)=5," " & A1, A1)
And similarly for the other formula.

 

[JackDanIce]

@footoo ty, about to try it. Sorry for dumb question, I'm using calibri font, is that monotype?

@J.Ty. it's already in FNF, they are 3 separate columns in a table, I need to join together and show unique values in a drop-down list.
Unfortunately. normal cell formatting tools doesn't equalise spacing between non-space characters due to varying character widths as displayed.

 

[footoo]

@footoo ty, about to try it. Sorry for dumb question, I'm using calibri font, is that monotype?

No : excel monotype fonts list - Google Search

 

[JackDanIce]

Thank you